What is a SQL frequency-distribution query to count ranges with group-by, and include 0 counts?

Question

Given:

table 'thing':

age
---
3.4
3.4
10.1
40
45
49

I want to count the number of things for each 10-year range, e.g.,

age_range | count
----------+-------
        0 |     2
        10|     1
        20|     0
        30|     0
        40|     3

This query comes close:

SELECT FLOOR(age / 10) as age_range, COUNT(*)
FROM thing
GROUP BY FLOOR(age / 10) ORDER BY FLOOR(age / 10);

Output:

 age_range | count 
-----------+-------
         0 |     1
         1 |     2
         4 |     3

However, it doesn't show me the ranges which have 0 counts. How can I modify the query so that it also shows the ranges in between with 0 counts?

I found similar stackoverflow questions for counting ranges, some for 0 counts, but they involve having to specify each range (either hard-coding the ranges into the query, or putting the ranges in a table). I would prefer to use a generic query like that above where I do not have to explicitly specify each range (e.g., 0-10, 10-20, 20-30, ...). I'm using PostgreSQL 9.1.3.

Is there a way to modify the simple query above to include 0 counts?

Similar:
Oracle: how to "group by" over a range?
Get frequency distribution of a decimal range in MySQL

Answer 1

generate_series to the rescue:

select 10 * s.d, count(t.age)
from generate_series(0, 10) s(d)
left outer join thing t on s.d = floor(t.age / 10)
group by s.d
order by s.d

Figuring out the upper bound for generate_series should be trivial with a separate query, I just used 10 as a placeholder.

This:

generate_series(0, 10) s(d)

essentially generates an inline table called s with a single column d which contains the values from 0 to 10 (inclusive).

You could wrap the two queries (one to figure out the range, one to compute the counts) into a function if necessary.