returns Zeros for Dates that dont exist MYSQL GROUP BY

Question

SELECT AVG(data) AS data, dateReg 
FROM table 
GROUP BY YEAR(dateReg), MONTH(dateReg), DAY(dateReg) ORDER BY dateReg

the Above Data Returns

1.75, 21 Jan 2010
9.45, 22 Jan 2010
3.96, 23 Jan 2010
2.68, 30 Jan 2010

Its missing dates from the 24th to 29th, Can we do it so the return value is:

1.75, 21 Jan 2010
9.45, 22 Jan 2010
3.96, 23 Jan 2010
0, 24 Jan 2010
0, 25 Jan 2010
0, 26 Jan 2010
0, 27 Jan 2010
0, 28 Jan 2010
0, 28 Jan 2010
2.68, 30 Jan 2010

Answer 1

You typically achieve this type of things by JOIN-ing with a value table, i.e. a table which contains all the values you are interested in.

Typical value tables may contain for example all the integer values between 0 and 1,000, or all dates for a given period. Often the Value tables include more values than desired, and we obtain the exact output desired by adding filters in the WHERE clause.

In this case you will require such a table which contain dates. Assuming this table is named ValTableDates and that it contains all the dates between January 2005 and December 2010, the query would look like:

SELECT AVG(data) AS data, VT.ValDate
FROM ValTableDates VT
LEFT JOIN  table T ON T.dateReg = VT.ValDate
WHERE VT.ValDate > [Some Start Date] and VT < [Some End Date]
GROUP BY YEAR(dateReg), MONTH(dateReg), DAY(dateReg) 
ORDER BY dateReg

The above query may require a bit of tweaking to get a value Zero rather than NULL, but the main point is that the Value Table is typically the simplest way to provide output records for missing data points.
An alternative is to use a function/expression that produces the desired [date] sequence, inside a subquery, but this is generally less efficient and more error prone.