Return counts for multiple ranges in a single SELECT statement

Question

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.

Answer 1

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.

Answer 2

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:

• The manual
• Postgres Wiki
• Depesz blog post

Also read below.

Postgres 9.3-

There are a couple of techniques:

• For absolute performance, is SUM faster or COUNT?

@Phil provided a common alternative 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

db<>fiddle here
_{Old sqlfiddle (pg 9.3)}