Share this page: Follow me to be notified of new articles:

Group by a range of values with PostgreSQL

Introduction

Here is a simple solution on how to group data within a range.
As a basic example, you may have a list of team members with their birth year and you may want to know the average salary grouped by decade:
 r_from | r_to | team_members | salary_avg | salary_min | salary_max 
--------+------+--------------+------------+------------+------------
   1960 | 1969 |            2 |   48150.00 |      39400 |      56900
   1970 | 1979 |            4 |   36575.00 |      26800 |      44000
   1980 | 1989 |            4 |   43125.00 |      23000 |      87000
   1990 | 1999 |            3 |   45533.33 |      40000 |      55000
I'm an illustration
Grouped veggies

Try it yourself

If you'd like to try the above example for yourself, just create this table:

Create a range with generate_series

A simple solution would be to create a range with generate series:
gab@test> WITH series AS (
                SELECT generate_series(1950, 2010, 10) AS r_from -- 1950 = min, 2010 = max, 10 = 10 year interval
), range AS (
                SELECT r_from, (r_from + 9) AS r_to FROM series -- 9 = interval (10 years) minus 1
)
SELECT r_from, r_to
FROM range;

 r_from | r_to 
--------+------
   1950 | 1959
   1960 | 1969
   1970 | 1979
   1980 | 1989
   1990 | 1999
   2000 | 2009
   2010 | 2019
(7 rows)

Group data by a range of values

You can use the example above to group data within a range:
gab@test> WITH series AS (
                SELECT generate_series(1950, 2010, 10) AS r_from -- 1950 = min, 2010 = max, 10 = 10 year interval
), range AS (
                SELECT r_from, (r_from + 9) AS r_to FROM series -- 9 = interval (10 years) minus 1
)
SELECT r_from, r_to,
       (SELECT count(*) FROM team WHERE birth_year BETWEEN r_from AND r_to) as team_members,
       round((SELECT AVG(salary) FROM team WHERE birth_year BETWEEN r_from AND r_to), 2) as salary_avg,
       (SELECT MIN(salary) FROM team WHERE birth_year BETWEEN r_from AND r_to) as salary_min,
       (SELECT MAX(salary) FROM team WHERE birth_year BETWEEN r_from AND r_to) as salary_max
FROM range;

 r_from | r_to | team_members | salary_avg | salary_min | salary_max 
--------+------+--------------+------------+------------+------------
   1950 | 1959 |            0 |     {NULL} |     {NULL} |     {NULL}
   1960 | 1969 |            2 |   48150.00 |      39400 |      56900
   1970 | 1979 |            4 |   36575.00 |      26800 |      44000
   1980 | 1989 |            4 |   43125.00 |      23000 |      87000
   1990 | 1999 |            3 |   45533.33 |      40000 |      55000
   2000 | 2009 |            0 |     {NULL} |     {NULL} |     {NULL}
   2010 | 2019 |            0 |     {NULL} |     {NULL} |     {NULL}
(7 rows)
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.