COUNT number of column values with INNER JOIN I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle:


http://sqlfiddle.com/#!15/6ce465/5


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

responses
-&gt;id
-&gt;question_id
-&gt;response

questions
-&gt;id
-&gt;question
-&gt;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 ;
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.