[SOLVED] Group by multiple datediff

Group by multiple datediff

I have this table storing when a user last connected to a server:

+----+----------------------------+-----------+
| id |         last_connection    | something |
+----+----------------------------+-----------+
|  1 | 2017-03-23 10:39:14.000000 | bleh      |
|  2 | 2014-03-20 07:05:51.000000 | blah      |
|  3 | ...                        | ...       |
+----+----------------------------+-----------+

I can select and count id that have been active in the last 2 months with something like: SELECT count(*) as '2months' FROMstatsWHERE TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) < 60, or in the last 3 months with SELECT count(*) as '3months' ... < 90 and so on, which gets me something like this:

+---------+
| 2months |
+---------+
|    1337 |
+---------+

My question is: is there a way to do group several TIMESTAMPDIFF in one unique query, and get something like the following?

+-----------+-------+
| last conn | count |
+-----------+-------+
| 1month    |  1337 |
| 2month    | 31337 |
| 3month    |   ... |
| ...       |   ... |
+-----------+-------+

Solution

SELECT sum(TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) < 30) as '1months',
       sum(TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) < 60) as '2months',
       sum(TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) < 90) as '3months' 
FROM stats

or shorter

SELECT sum(last_connection > current_timestamp - interval 30 day) as '1months',
       sum(last_connection > current_timestamp - interval 60 day) as '2months',
       sum(last_connection > current_timestamp - interval 90 day) as '3months' 
FROM stats