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

Given that for some dates you have no data, you'll need to fill in the gaps. One approach to this is to have a calendar table prefilled with all dates you need, and join against that. If you want the results to show day numbers as you have showing in your question, you could prepopulate these in your calendar too as labels.

You would join your data table date field to the date field of the calendar table, group by that field, and sum values. You might want to specify limits for the range of dates covered.

So you might have:

CREATE TABLE Calendar (
    label varchar,
    cal_date date,
    primary key ( cal_date )
)

Query:

SELECT
    c.label,
    SUM( d.values )
FROM
    Calendar c
JOIN
    Data_table d
ON  d.date_field = c.cal_date
WHERE
    c.cal_date BETWEEN '2010-01-01' AND '2010-01-31'
GROUP BY
    d.date_field
ORDER BY
    d.date_field

Update:

I see you have datetimes rather than dates. You could just use the MySQL DATE() function in the join, but that would probably not be optimal. Another approach would be to have start and end times in the Calendar table defining a 'time bucket' for each day.

Answer 2

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

Answer 3

If I'm understanding the rather vague question correctly, you want to know the number of records for each date within a month. If that's true, here's how you can do it:

SELECT COUNT(value_column) FROM table WHERE date_column LIKE '2010-01-%' GROUP BY date_column