3

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle:

So basically I have two tables, responses and questions. The structure is

responses
->id
->question_id
->response

questions
->id
->question
->costperlead

For the column response there can only be 3 values: Yes, No, Possibly.

And my report should have the columns

question_id
# of Yes Responses
# of No Responses
# of Possibly Responses
Revenue

where

  • # of Yes Responses is the count of all Yes values in the response column,
  • # of No Responses is the count of all No values in the response column,
  • # of Possibly Responses is the count of all Possibly values in the response column,
  • Revenue is costperlead * ("# of Yes Responses" + "# of Possibly Responses").

I don't know how to construct the query. I'm new plus I came from MySQL, so some things will be different in PostgreSQL. In my SQL Fiddle sample most responses are Yes and Null – it's OK. Eventually, there will be Possibly and No.

So far I have only

SELECT a.question_id
FROM responses a INNER JOIN questions b ON a.question_id = b.id
WHERE a.created_at = '2015-07-17'
GROUP BY a.question_id ;

closed as off-topic by jcolebrand Jul 20 '15 at 19:41

If this question can be reworded to fit the rules in the help center, please edit the question.

0

Will it work for you ?

SELECT a.id as question_id, a.costperlead, a.costperlead *(b.numYes+b.numPossible) as revenue,
b.numYes,b.numNo,b.numPossible
FROM questions a
LEFT JOIN 
(
SELECT a.question_id, 
count(case when a.response='Yes' then 1 end)AS numYes,
count(case when a.response='No' then 1 end) AS numNo,
count(case when a.response = 'Possibly' then 1 end) numPossible
FROM responses a 
GROUP BY a.question_id
)b ON b.question_id = a.id;

(http://sqlfiddle.com/#!15/6ce465/46)

Note: questions without responses will be also included in resultset. If it's not needed , replace LEFT JOIN with INNER JOIN.

Not the answer you're looking for? Browse other questions tagged or ask your own question.