HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

Return counts for multiple ranges in a single SELECT statement

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
statementreturnrangessingleformultipleselectcounts

Problem

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.

Solution

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.

Code Snippets

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;

Context

StackExchange Database Administrators Q#93510, answer score: 15

Revisions (0)

No revisions yet.