user3515684 user3515684 - 25 days ago 8
MySQL Question

mysql select datetime query to find ids which are 1 year old, 2 year old and so on

I want ids which were created in last 1 year, or year > 1 but year < 2, and so on. Like suppose if this is the table:

+----+--------------------------+---------------------+
| id | data | cur_timestamp |
+----+--------------------------+---------------------+
| 1 | The time of creation is: | 2014-02-01 20:37:22 |
| 2 | The time of creation is: | 2015-01-01 20:37:22 |
| 3 | The time of creation is: | 2015-02-01 20:37:22 |
| 4 | The time of creation is: | 2015-12-01 20:37:22 |
| 5 | The time of creation is: | 2016-02-01 20:37:22 |
| 6 | The time of creation is: | 2016-04-01 20:37:22 |
+----+--------------------------+---------------------+


I would like to get a table like this:

+-----------------------+-------+
| date range | count |
+-----------------------+-------+
| last 1 year | 3 |
| > 1 year & < 2 years | 2 |
| > 2 years | 1 |
+-----------------------+-------+


Any content in first column is fine. But I would like to have the count as specified above. I have tried various things.

select count(*) from ids where cur_timestamp >= date_sub(now(), interval 1 year);


This will give me count of ids in last one year. Similarly I can extend it to get the other values. But is there some way by which I can get all the values in one single query?

vkp vkp
Answer

This is called conditional aggregation. You have to add more conditions to get as many years needed.

select 
count(case when cur_timestamp >= date_sub(now(), interval 1 year) then 1 end) last_1_yr,
count(case when cur_timestamp > date_sub(now(), interval 1 year) and cur_timestamp <= date_sub(now(), interval 2 year) then 1 end) bw_1_2_yrs,
count(case when cur_timestamp > date_sub(now(), interval 2 year) then 1 end) gt_2_yrs
from ids 
Comments