9

I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3, the number of scores between 4 and 6, and the number of scores between 7 and 10. Something like the following:

SELECT
  COUNT(*) as total,
  COUNT(
    SELECT * from foo where score between 0 and 3;
  ) as low,
  COUNT(
    SELECT * from foo where score between 4 and 6;
  ) as mid,
  COUNT(
    SELECT * from foo where score between 7 and 10;
  ) as high
FROM foo;

I tried this, but got an error with the SELECT in the COUNT statements. Any ideas how I can do this? I'm sure there's a super simple way in Postgres. I just can't figure out the correct terms to Google for.

7

Just use conditional SUM() statements per column for each number range. The total can be summed by just using SUM(1), assuming all of the data in the table is within one of the ranges - if not, just restrict it as with the others.

select sum(case when score between 0 and 3 then 1 else 0 end) as minrange,
       sum(case when score between 4 and 6 then 1 else 0 end) as midrange,
       sum(case when score between 7 and 10 then 1 else 0 end) as maxrange,
       sum(1) as total
from foo;

SQL Fiddle link.

8

Aggregate FILTER clause in Postgres 9.4+

Since Postgres 9.4 there is a clean and fast (SQL standard) way:

SELECT count(*) FILTER (WHERE score BETWEEN 0 AND 3)  AS low
     , count(*) FILTER (WHERE score BETWEEN 4 AND 7)  AS mid
     , count(*) FILTER (WHERE score BETWEEN 8 AND 10) AS high
     , count(*)                                       AS total
FROM   foo;

total adds up low, mid and high, unless NULL or other values are involved.

Links:

Also read below.

Postgres 9.3-

There are a couple of techniques:

@Phil provided the standard way with a CASE statement (except for sum(1), which isn't the standard way). I like to use a shorter form:

SELECT count(score BETWEEN 0 AND 3  OR NULL) AS low
     , count(score BETWEEN 4 AND 6  OR NULL) AS mid
     , count(score BETWEEN 7 AND 10 OR NULL) AS high
     , count(*)                              AS total
FROM   foo;

If your values are as defined in your question (only 0 - 10 possible), simplify further:

SELECT count(score < 4 OR NULL)             AS low
     , count(score BETWEEN 4 AND 6 OR NULL) AS mid
     , count(score > 6 OR NULL)             AS high
     , count(*)                             AS total
FROM   foo;

A bit shorter, barely faster.

Subtle differences

There are subtle differences when compared to sum() in Phil's answer:

  • Most importantly, per documentation:

    It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect, ...

  • count(*) is the standard way and a bit faster than sum(1). Again, null vs. 0 applies.

Either of these queries (including Phil's) counts null values for total. If that's not desirable, use instead:

count(score) AS total_not_null

SQL Fiddle in pg 9.3.
db<>fiddle here in pg 10.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.