0

I am trying to make a report of what a particular user ate for the last 7 days. Below is the code i used and the out put I get.

SELECT  DATE(date), SUM(fruit), SUM(veg)
FROM diary
WHERE u_id = 7 and DATEDIFF(date,DATE(DATE_ADD(NOW())) < 168
GROUP BY DATE(date)

This is the output I get from the statement below

I would like to know if it is possible to get the output such that for the missing days. the sum of each fruit and veg will be zero.

like this

The output I want

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
KMC
  • 301
  • 1
  • 3
  • 9
  • Why are the missing days being excluded from your current query? – Tim Biegeleisen Jul 07 '15 at 03:12
  • @TimBiegeleisen because there are no rows for the "missing" dates... just looking for the canonical dup – Bohemian Jul 07 '15 at 03:16
  • @Bohemian Is this really an exact duplicate? – Tim Biegeleisen Jul 07 '15 at 03:19
  • 1
    @tim definitely. It's a very common "problem". The where clause is irrelevant - there may be no rows whatsoever (no matter the where clause) in the table for a particular date in the range. The problem is basically "how to manufacture all values in a range", for which mysql has no built-in function. – Bohemian Jul 07 '15 at 03:51
  • Hi @Tim Biegeleisen, In the dairy table if the user does not input what they have had for the day then there will be no entry for that specific date. That is why they are not included in the query. – KMC Jul 07 '15 at 09:19
  • Do a `LEFT JOIN` with `COALESCE` on the missing value column. – Tim Biegeleisen Jul 07 '15 at 09:25

0 Answers0