# Postgresql – COUNT number of column values with INNER JOIN

countgroup byjoin;postgresql

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


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;


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