1

I would like to get data for the past 1week(7 days), with all days included. When there is no data for particular day, a zero should be returned.

This is what i have so far

"SELECT DATE(date) AS created_date, SUM(quantity) AS total_amount " +
"FROM milk_collection " +
"WHERE DATE(date) > DATE_SUB(DATE(NOW()), INTERVAL 30 DAY) " +
"GROUP BY created_date";

The problem is this query does not return zero(0) if no records for that day are found.

Yunus Einsteinium
  • 1,102
  • 4
  • 21
  • 55
  • Could you present some information, like a case showing the state of you tables, and the expected result. – cdaiga Feb 17 '16 at 07:47

3 Answers3

2

This is a classic application for a "calendar table". To get zero values for certain key values you need to ensure those key values appear in a table that you can left-join to the values table.

Create a table containing one row per date:

create table calendar ( cal_date date );

Then populate it with all dates in the range you're interested in. Finally left-join it to milk_collection on the date column in your query. This will guarantee you have at least one row per date in the selected range, with null in the quantity column for dates having no data.

Community
  • 1
  • 1
Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
1

Thanks to @Jim Garrison for his guide, i was able to come up with a query that does exactly what i want. Hope this post will help someone with similar problem and save them time.

SQL

"SELECT DATE(D) AS temporary_date, SUM(IFNULL(quantity,0)) AS total_amount, DATE(date) AS created_date "+ 
"FROM "+ 
   "( SELECT DATE_SUB(NOW(), INTERVAL D DAY) AS D "+ 
    " FROM (SELECT 0 as D "+ 
              "UNION SELECT 1 "+ 
              "UNION SELECT 2 "+ 
              "UNION SELECT 3 "+ 
              "UNION SELECT 4 "+ 
              "UNION SELECT 5 "+ 
              "UNION SELECT 6 ) AS D ) AS D "+ 
"LEFT JOIN milk_collection ON DATE(date) = date(D) "+ 
"GROUP BY temporary_date ORDER BY D ASC ";
Yunus Einsteinium
  • 1,102
  • 4
  • 21
  • 55
-1

Try this, I am not sure if this works as I am not with my system that has MySQL installation

"SELECT DATE(date) AS created_date, SUM(IFNULL(quantity,0)) AS total_amount " +
"FROM milk_collection " +
"WHERE DATE(date) > DATE_SUB(DATE(NOW()), INTERVAL 30 DAY) " +
"GROUP BY created_date";
Amar Dev
  • 1,360
  • 2
  • 18
  • 38