KillianKemps KillianKemps - 7 months ago 12
SQL Question

How to count occurences in several date ranges in PostgreSQL

I'm able to do a query to get the number of customers who comes in a shop from age 18 to 24 per month and per shop.
I'm doing it like this:

select year, month, shop_id, count(birthday)
from customers
where birthday
BETWEEN '1992-01-01 00:00:00' AND '1998-01-01 00:00:00'
group by year, month, shop_id;


Now, I'm having an issue making this query for several ranges at the same time.

I have currently this database schema:

shop_id | birthday | year | month |
--------+----------+------+--------
567 | 1998-10-10 | 2014 | 10 |
567 | 1996-10-10 | 2014 | 10 |
567 | 1985-10-10 | 2014 | 10 |
234 | 1990-10-10 | 2014 | 10 |
123 | 1970-01-10 | 2014 | 10 |
123 | 1974-01-10 | 2014 | 11 |


And I would like to get something like this:

shop_id | year | month | 18 < age < 25 | 26 < age < 35
--------+------+-------+---------------+-------------
567 | 2014 | 10 | 2 | 1
234 | 2014 | 10 | 1 | 0
123 | 2014 | 10 | 0 | 0


In the first query, it does not manage the case where one shop has NO customers. How to get 0 if there is not?

How to query the several date ranges at the same time?

mo2 mo2
Answer

Instead of filters, use case statements:

select year, month, shop_id, 
count(case when birthday between <range1> then 1 end) RANGE1,
count(case when birthday between <range2> then 1 end) RANGE2,
count(case when birthday between <range3> then 1 end) RANGE3
from customers 
group by year, month, shop_id;
Comments