Group by range of years I've got a big table (~9M rows) and want to group the rows on a field containing the year. So far that's pretty easy:

// greatly simplified:
SELECT count(*), year FROM dataset GROUP BY year ORDER BY 2;


We defined some irregular time periods spanning multiple years:

&lt;1945, 1946-1964, 1965-1974, 1975-1991, 1992-2005 and &gt;2005


I've got no clue on how to group these results in the group by clause. I could make subquery's for every time period.  

SELECT
  ( SELECT count(*) FROM dataset WHERE year &lt;= 1945 AND ...... ) AS pre1945,
  ( ....) AS period2,
  ....
FROM dataset


But that feels not right and I'm wondering if it was possible to let Postgresql do it. Especially because the query is a strong simplification of the real query: it has multiple conditions, amongst them a ST_within clause spanning four tables. So choosing the subquery-approach results in a bloated query.

Is there a better way to create this result?
If you want the result as rows in instead of as columns as in @a_horse's answer then create the year ranges in a CTE and join the table to it

with years(year_range) as ( values
    (int4range(1900, 1945, '[]')),
    (int4range(1946, 1964, '[]')),
    (int4range(1965, 1974, '[]')),
    (int4range(1975, 1991, '[]')),
    (int4range(1992, 2005, '[]')),
    (int4range(2005, 2014, '[]'))
)
select year_range, count(*)
from
    dataset d
    left join
    years y on d.year &lt;@ y.year_range
group by 1 
order by 1


http://www.postgresql.org/docs/current/static/rangetypes.html