How to get values for every day in a month

Question

Data:

values date
14 1.1.2010
20 1.1.2010
10 2.1.2010
7  4.1.2010
...

sample query about january 2010 should get 31 rows. One for every day. And values vould be added. Right now I could do this with 31 queries but I would like this to work with one. Is it possible?

results:

1. 34
2. 10
3.  0
4.  7
...

Answer 1

This is actually surprisingly difficult to do in SQL. One way to do it is to have a long select statement with UNION ALLs to generate the numbers from 1 to 31. This demonstrates the principle but I stopped at 4 for clarity:

SELECT MonthDate.Date, COALESCE(SUM(`values`), 0) AS Total
FROM (
    SELECT 1 AS Date UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    --
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30 UNION ALL
    SELECT 31) AS MonthDate
LEFT JOIN Table1 AS T1
ON MonthDate.Date = DAY(T1.Date)
AND MONTH(T1.Date) = 1 AND YEAR(T1.Date) = 2010
WHERE MonthDate.Date <= DAY(LAST_DAY('2010-01-01'))
GROUP BY MonthDate.Date

It might be better to use a table to store these values and join with it instead.

Result:

1, 34
2, 10
3, 0
4, 7