Postgres COUNT number of column values with INNER JOIN

Question

I am creating a report in Postgres 9.3. This is my SQL Fiddle.
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/Possbily, and my report should have the columns:

  question_id
, # of Yes Responses
, # of No Responses
, # of Possbily Responses
, Revenue

Then:

# of Yes Responses - count of all Yes values in the response column
# of No Responses - count of all No values in the response column
# of Possbily Responses - count of all 'Possbily' values in the response column

Revenue is the costperlead * (Number of Yes Responses + Number of Possibly Responses).

I don't know how to construct the query, I'm new plus I came from MySQL so some things are different for postgres. 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;

Renzo · Accepted Answer · 2015-07-20 11:45:24Z

4

You should try:

SELECT a.question_id, 
       SUM(CASE WHEN a.response = 'Yes' THEN 1 ELSE 0 END) AS NumsOfYes, 
       SUM(CASE WHEN a.response = 'No' THEN 1 ELSE 0 END) AS NumsOfNo,
       SUM(CASE WHEN a.response = 'Possibly' THEN 1 ELSE 0 END) AS NumOfPossibly,
       costperlead  * SUM(CASE WHEN a.response = 'Yes' THEN 1 ELSE 0 END) + SUM(CASE WHEN a.response = 'Possibly' THEN 1 ELSE 0 END) AS revenue
FROM responses a 
     INNER JOIN questions b ON a.question_id = b.id 
GROUP BY a.question_id, b.costperlead

edited Jul 20, 2015 at 11:45

answered Jul 20, 2015 at 11:16

Renzo

27.6k5 gold badges54 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jackhammer013 Over a year ago

I got an error Sir, ERROR: column b.response does not exist Position: 29

jackhammer013 Over a year ago

Thanks for helping Sir but seems giving me incorrect results, coz if you check the responses table question_id = 44 has 2 response for Yes, then the others are null, but the result as per your query gives 2 count for Yes, No and Possibly?

Erwin Brandstetter Over a year ago

This uses the aggregate function count() incorrectly, which counts all values except NULL. Whether the expression yields true or false does not mater to count().

Renzo Over a year ago

Modified with the use of SUM.

jackhammer013 Over a year ago

Hi thank you so much it works well but @ErwinBrandstetter works for me better plus the explanations. I'll up vote this instead :) Thanks!

Erwin Brandstetter · Accepted Answer · 2021-07-26 13:42:19Z

3

Since the only predicate filters rows from table responses, it would be most efficient to aggregate responses first, then join to questions:

SELECT *, q.costperlead * (r.ct_yes + r.ct_maybe) AS revenue
FROM  (
   SELECT question_id
        , count(*) FILTER (WHERE response = 'Yes')      AS ct_yes
        , count(*) FILTER (WHERE response = 'No')       AS ct_no
        , count(*) FILTER (WHERE response = 'Possibly') AS ct_maybe
   FROM   responses
   WHERE  created_at = '2015-07-17'
   GROUP  BY 1
   ) r
JOIN   questions q ON q.id = r.question_id;

db<>fiddle here

This uses the aggregate FILTER clause (in Postgres 9.4 or later). See:

Aggregate columns with additional (distinct) filters

Aside: consider implementing response as boolean type with true/false/null.

For Postgres 9.3:

SELECT *, q.costperlead * (r.ct_yes + r.ct_maybe) AS revenue
FROM  (
   SELECT question_id
        , count(response = 'Yes' OR NULL)      AS ct_yes
        , count(response = 'No' OR NULL)       AS ct_no
        , count(response = 'Possibly' OR NULL) AS ct_maybe
   FROM   responses
   WHERE  created_at = '2015-07-17'
   GROUP  BY 1
   ) r
JOIN   questions q ON q.id = r.question_id;

_{Old sqlfiddle}

Comprehensive comparison of techniques:

For absolute performance, is SUM faster or COUNT?

edited Jul 26, 2021 at 13:42

answered Jul 20, 2015 at 11:37

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

7 Comments

jackhammer013 Over a year ago

[Err] ERROR: syntax error at or near "(" LINE 5: , count(*) FILTER (WHERE r.response = 'Yes') AS ct_y...

Erwin Brandstetter Over a year ago

@JoeneFloresca: I added a solution for pg 9.3.

jackhammer013 Over a year ago

Wow thank you so much for your help and explanation, it works fine but last question, your GROUP BY 1? Why is it 1? OR Do I need that?

Erwin Brandstetter Over a year ago

@JoeneFloresca: In your fiddle you have no 'Possibly' values, just NULL. That contradicts the question? GROUP BY 1 is a shorthand syntax with positional reference as explained here: stackoverflow.com/a/10398558/939860

jackhammer013 Over a year ago

Yes, you're right but if ever that the're will never be a NULL value? is it still ok to use the GROUP BY 1 without affecting the accuracy of the results?

|

Collectives™ on Stack Overflow

Postgres COUNT number of column values with INNER JOIN

2 Answers 2

5 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related