Martin Martin - 4 months ago 12
SQL Question

Get number of entries per multiple date intervals using single query

SELECT COUNT(*) FROM `table` WHERE `datetime` > SUBDATE(NOW(), INTERVAL 1 DAY)


This will get number of entries during last day. But is it possible to get number of entries for multiple intervals without having to send variation of this query multiple times (
INTERVAL 1 DAY
,
INTERVAL 1 WEEK
,
INTERVAL 1 MONTH
, ...)?

Answer

You need CASE WHEN expression to accomplish that.

SELECT
    COUNT(CASE WHEN DATE(`datetime`) >= CURDATE() - INTERVAL 1 DAY AND DATE(`datetime`) < CURDATE() THEN 1 END) AS lastDay,
    COUNT(CASE WHEN DATE(`datetime`) >= CURDATE() - INTERVAL 7 DAY AND DATE(`datetime`) < CURDATE() THEN 1 END ) AS lastSevenDays,
    COUNT(*) AS lastThirtyDays
FROM `table`
WHERE   
DATE(`datetime`) >= CURDATE() - INTERVAL 30 DAY

How to use CASE WHEN expression

Note: If your requirement is to get result of last day, last 7 days and last 30 days then go with this query.