CODEWITHSUNDEEP
mysqlsqlgroup-by
Janusz Janusz
Janusz Janusz

Reputation: 43

Get records from last 7 days

I have problem with query 

SELECT
    IFNULL(SUM(a.x), 0) AS x,
    IFNULL(COUNT(*), 0) AS num
FROM a AS a
WHERE (a.`time` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY))
GROUP BY DAY(a.time)

It's ok, but when exact day have no records this no return this day. I need to return 0. How to do it?

Upvotes: 1

Views: 10573

Answers (1)

verisimilitude
verisimilitude

Reputation: 5108

SELECT
    COALESCE(SUM(a.x), 0) AS x,
    COALESCE(COUNT(*), 0) AS num
FROM a AS a
WHERE (a.`time` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY))
GROUP BY DAY(a.time)

COALESCE - returns the first non-null value in the list

But, I think you should handle this as you process the data as suggested by @DevZer0.

Upvotes: 6

Related Questions