CODEWITHSUNDEEP
mysqlsqlmariadb
Martin
Martin

Reputation: 1392

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, ...)?

Upvotes: 1

Views: 264

Answers (1)

1000111
1000111

Reputation: 13519

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.

EDIT:

If you have an index on datetime field then the above query will fail to use that index. Please use the query given below in order to utilize the index on datetime.

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   
`datetime` >= (NOW() - INTERVAL 30 DAY - INTERVAL HOUR(NOW()) HOUR - INTERVAL MINUTE(NOW()) MINUTE - INTERVAL SECOND(NOW()) SECOND)

Upvotes: 2

Related Questions