Group by multiple datediff

Question

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    |   ... |
| ...       |   ... |
+-----------+-------+

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 02 '17 at 20:52

score 1 · Accepted Answer · answered May 02 '17 at 20:51

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

score 0 · Answer 2 · answered May 02 '17 at 21:12

One way to do this would be to use UNION operator, e.g.:

SELECT '1month', count(*)  
FROM stats
WHERE  TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) BETWEEN 0 AND 30

UNION

SELECT '2months', count(*)  
FROM stats
WHERE  TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) BETWEEN 31 AND 60

UNION

SELECT '3months', count(*)  
FROM stats
WHERE  TIMESTAMPDIFF(DAY, SUBSTRING_INDEX(last_connection, ' ', 1), CURDATE()) BETWEEN 61 AND 90

Also, you need to use range instead of just < operator to prevent the dups.

Group by multiple datediff

2 Answers2