Returns Zeros for Dates that do not exist MYSQL GROUP BY

Question

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

Above data is returned

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

Its missing dates are from the 24th to the 29th, can we do this so that 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

Usually you achieve this type of thing by adding a value table , i.e. a table containing all the values ​​you are interested in.

Typical value tables may contain, for example, integer values ​​from 0 to 1000 or all dates for a given period. Often, Value tables contain more values ​​than required, and we get the exact result desired by adding filters to the WHERE clause.

, . , ValTableDates 2005 2010 , :

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

, Zero, NULL, , .
/, [date], , , , .