snippetsqlModerate
Return counts for multiple ranges in a single SELECT statement
Viewed 0 times
statementreturnrangessingleformultipleselectcounts
Problem
I have a Postgres database table
I tried this, but got an error with the
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
SQL Fiddle link.
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.