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?

Not really an answer to your question, but why not just do it on the coding side? — Ben Lee, May 15 '12 at 17:23
@BenLee That's what I'm doing right now. but I like to keep all the logic together — dcarneiro, May 15 '12 at 17:30
If you are going to be doing a lot with ranges, and don't mind being on the bleeding edge, PostgreSQL 9.2 just hit beta release ( http://www.postgresql.org/about/news/1395/ ) with built-in range support. It would be overkill for this one example, but if the nature of your application is that it uses a lot of ranges, ease "overlaps" and "contains" operators, etc., might save some time. — kgrittn, May 19 '12 at 13:16

vyegorov · Accepted Answer · 2012-05-15T19:04:52.120

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.

Nikola Markovinović · Answer 2 · 2012-05-15T17:36:44.270

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

score 1 · Answer 3 · answered May 15 '12 at 17:44

select r.range as [score range], count(*) as [number of occurences]
from 
    (
    select ' 0- 9' as range, 9 as endrange
    union all select '10-19',19
    union all select '20-29',29
    union all select '30-39',39
    union all select '40-49',49
    union all select '50-59',59
    union all select '60-69',69
    union all select '70-79',79
    union all select '80-89',89
    union all select '90-99',99
    ) as r
left join scores s on 
    r.endrange = case 
    when s.score > 90 then 99
    when s.score > 80 then 89
    when s.score > 70 then 79
    when s.score > 60 then 69
    when s.score > 50 then 59
    when s.score > 40 then 49
    when s.score > 30 then 39
    when s.score > 20 then 29
    when s.score > 10 then 19
    when s.score > 0 then 9
    end
group by r.range

score 0 · Answer 4 · answered Dec 02 '14 at 21:57

Actually, the simplest solution is this (for 2 digit numbers):

select substr(rssi::text, 0, 2) || '0-' || substr(rssi::text, 0, 2) || '9' as range, count(*)
from sensor_stations
group by substr(rssi::text, 0, 2)
order by count(*) desc;

The output will be something like this:

 range | count 
-------+-------
 10-19 |  3414
 30-39 |  1363
 20-29 |  1269
 40-49 |   769
 50-59 |   294
 60-69 |   106
 70-79 |     5
(7 rows)

sql - group by in ranges to include ranges without values

4 Answers4

Linked