sql - group by in ranges to include ranges without values

Question

Suppose a scenario similar to this question. I want to get the following results:

score range  | number of occurrences
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3
   ...       |       ...

And I can use the selected answer as a solution:

select t.range as [score range], count(*) as [number of occurences]
from (
  select case  
    when score between 0 and 9 then ' 0- 9'
    when score between 10 and 19 then '10-19'
    else '20-99' end as range
  from scores) t
group by t.range

How can I assure that the score range of 30-39 will be display even when there are no results on that range?

Answer 1

Try this query (also on SQL Fiddle):

WITH ranges AS (
    SELECT (ten*10)::text||'-'||(ten*10+9)::text AS range,
           ten*10 AS r_min, ten*10+9 AS r_max
      FROM generate_series(0,9) AS t(ten))
SELECT r.range, count(s.*)
  FROM ranges r
  LEFT JOIN scores s ON s.score BETWEEN r.r_min AND r.r_max
 GROUP BY r.range
 ORDER BY r.range;

---

EDIT:

You can easily adjust the range by changing parameters to generate_series(). It is possible to use the following construct to make sure ranges will always cover your scores:

SELECT (ten*10)::text||'-'||(ten*10+9)::text AS range,
       ten*10 AS r_min, ten*10+9 AS r_max
  FROM generate_series(0,(SELECT max(score)/10 FROM scores)) AS t(ten))

for the ranges CTE.

Answer 2

You cannot like that, but if you add derived table with ranges things become possible:

select ranges.range, count(scores.score) as [number of occurences]
  from
  (
     select 0 minRange, 9 maxRange, '0-9' range
     union all
     select 10, 19, '10-19'
     union all
     select 20, 29, '20-29'
  ) ranges
  left join scores
    on scores.score between ranges.minRange and ranges.maxRange  
 group by ranges.range

Not sure about syntax of postgresql though.

EDIT: Got the syntax right:

select ranges."range", count(scores.score) as "number of occurences"
  from
  (
     select 0 minRange, 9 maxRange, '0-9' "range"
     union all
     select 10, 19, '10-19'
     union all
     select 20, 29, '20-29'
  ) as ranges
  left join scores
    on scores.score between ranges.minRange and ranges.maxRange  
 group by ranges.range