How can I count the visits according to the date?-CodePudding

The VISITS table

USER_ID	VISITED_IN
518	2022-04-13 20:37:04
518	2021-12-29 22:26:50
518	2021-03-04 04:22:46
518	2021-08-13 02:14:54
518	2022-05-26 20:49:01
518	2022-05-05 17:47:46
518	2021-09-12 08:58:33
518	2021-04-07 18:36:59
518	2021-06-14 04:47:52
518	2021-12-26 22:16:47

Let's suppose the current time is 28-5-2022 15:00:00, I'm trying to filter the visits by the date.

The expected result when executing the command

LAST_60_MINUTES	LAST_24_HOURS	LAST_7_DAYS	LAST_30_DAYS	LAST_6_MONTHS	LAST_12_MONTHS
0	0	1	2	5	8

Here is what I tried but does not work as I want

SELECT 
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE))) AS LAST_60_MINUTES,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR))) AS LAST_24_HOURS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY))) AS LAST_7_DAYS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY))) AS LAST_30_DAYS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH))) AS LAST_6_MONTHS,
    COUNT((SELECT USER_ID FROM VISITS WHERE VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH))) AS LAST_12_MONTHS
    FROM VISITS WHERE
    USER_ID = 518

For more information look at this question

CodePudding user response：

Either use CASE expressions:

SELECT 
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE) THEN 1 END) AS LAST_60_MINUTES,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) AS LAST_24_HOURS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) AS LAST_7_DAYS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) AS LAST_30_DAYS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 1 END) AS LAST_6_MONTHS,
    COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH) THEN 1 END) AS LAST_12_MONTHS
FROM VISITS 
WHERE USER_ID = 518;

or SUM() instead of COUNT():

SELECT 
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE)) AS LAST_60_MINUTES,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR)) AS LAST_24_HOURS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS LAST_7_DAYS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS LAST_30_DAYS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS LAST_6_MONTHS,
    SUM(VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH)) AS LAST_12_MONTHS
FROM VISITS 
WHERE USER_ID = 518;

See the demo.