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



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

And my report should have the columns

# of Yes Responses
# of No Responses
# of Possibly Responses


  • # 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 =
WHERE a.created_at = '2015-07-17'
GROUP BY a.question_id ;

Best Answer

Will it work for you ?

SELECT as question_id, a.costperlead, a.costperlead *(b.numYes+b.numPossible) as revenue,
FROM questions a
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 =;


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