1

I have a table answers in a PostgreSQL engine powered database, with a number associated to a question. I need to count how many numbers are there and how many of them are below 6 grouped by question.

What I want is something like:

SELECT question, count(*) AS Qanswers, count_below_6(*) AS Qanswers_below_6
FROM answers
GROUP BY question;

      question         | Qanswers | Qanswers_below_6
-----------------------+----------+------------------
How do you feel?       |  1234    |      53
Was clear the webinar? |  8444    |      20
How much that hurt?    |  3666    |     142

Currently I'm doing 

SELECT question, count(*) AS Qanswers
FROM answers
GROUP BY question;

And then

SELECT question, count(*) AS Qanswers
FROM answers
WHERE value < 6
GROUP BY question;

After that I merge the two results manually.
Can I make a single sentence that gives me the result as I need?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
gramo
  • 103
  • 6
  • `postgresql engine powered database`? Does this mean you have a database that is not PostgreSQL, but is using its engine? I'd be curious to see said database. Is it on GitHub? – vol7ron Jun 06 '14 at 22:07

1 Answers1

0
SELECT question
      ,count(*) AS answers
      ,count(value < 6 OR NULL) AS under_6
FROM   answers
GROUP  BY question;

The trick is that count(expression) only counts non-null values (while count(*) counts all rows). Here is a detailed explanation why we get NULL in the right places:
Compute percents from SUM() in the same SELECT sql query

There is a number of equivalent techniques to achieve that. Detailed comparison with a benchmark:
For absolute performance, is SUM faster or COUNT?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    It might be interesting to include using range-tables as part of that performance analysis - eg, using `SELECT question, COUNT(*) AS answers, COUNT(low) AS under_6 FROM Answers LEFT JOIN (VALUES (6)) Lim(low) ON Answers.value < Lim.low GROUP BY question`. Granted, it's easier to do more complicated conditions with `CASE` and related... – Clockwork-Muse Jun 06 '14 at 23:03