How to get values for every day in a month

Data:

values date
14 1.1.2010
20 1.1.2010
10 2.1.2010
7开发者_StackOverflow中文版  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
...

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

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.

This works for me... Its a modification of a query I found on another site. The "INTERVAL 1 MONTH" clause ensures I get the current month data, including zeros for days that have no hits. Change this to "INTERVAL 2 MONTH" to get last months data, etc.

I have a table called "payload" with a column "timestamp" - Im then joining the timestamp column on to the dynamically generated dates, casting it so that the dates match in the ON clause.

SELECT `calendarday`,COUNT(P.`timestamp`) AS `cnt` FROM 
(SELECT @tmpdate := DATE_ADD(@tmpdate, INTERVAL 1 DAY) `calendarday`
    FROM (SELECT @tmpdate := 
        LAST_DAY(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))) 
        AS `dynamic`, `payload`) AS `calendar`
LEFT JOIN `payload` P ON DATE(P.`timestamp`) = `calendarday`
GROUP BY `calendarday`

To dynamically get the dates within a date range using SQL you can do this (example in mysql):

Create a table to hold the numbers 0 through 9.

CREATE TABLE ints ( i tinyint(4) );

insert into ints (i)
values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Run a query like so:

select ((curdate() - interval 2 year) + interval (t.i * 100 + u.i * 10 + v.i) day) AS Date
from
    ints t
    join ints u
    join ints v
having Date between '2015-01-01' and '2015-05-01'
order by t.i, u.i, v.i

This will generate all dates between Jan 1, 2015 and May 1, 2015.

Output
2015-01-01
2015-01-02
2015-01-03
2015-01-04
2015-01-05
2015-01-06
...
2015-05-01

The query joins the table ints 3 times and gets an incrementing number (0 through 999). It then adds this number as a day interval starting from a certain date, in this case a date 2 years ago. Any date range from 2 years ago and 1,000 days ahead can be obtained with the example above. To generate a query that generates dates for more than 1,000 days simply join the ints table once more to allow for up to 10,000 days of range, and so forth.

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