SQL Statement to get daily totals

Question

I'm storing some data in a table and I want to be able to display the total data points per day on the graph. So the first graph point might be 7 for Monday, Jan 1, 2013, and then 3 for Tuesday, Jan 2, 2013...etc.

I have full time/date stamps for each of my data points in my table of SQL type datetime.

My pseudo statement looks like this, but I'm concerned since I'm using the datetime data type:

SELECT 
    DATE(created_at) AS create_date
    COUNT(id) AS total
FROM
    data_table
GROUP BY 
    create_date

How can I get the total data points per day, regardless of the timestamp?

This shows you how to cast a datetime to a date. http://stackoverflow.com/questions/1468807/how-to-cast-datetime-as-a-date-in-mysql which will truncate the timestamp portion. — xQbert, Apr 18 '13 at 18:45

score 2 · Accepted Answer · edited Apr 18 '13 at 18:52

2

Try this

SELECT 
    DATE(created_at) AS create_date,
    COUNT(id) AS total
FROM
    data_table
GROUP BY 
    DATE(created_at)

edited Apr 18 '13 at 18:52

yunzen

32,854
11
73
106

answered Apr 18 '13 at 18:45

rs.

26,707
12
68
90

I'm getting an error around "COUNT(id) AS total". It seems to be truncating to "create_date" in the line before. Is this the proper syntax in the first place? – Jon Apr 18 '13 at 18:49

yunzen · Answer 2 · 2013-04-18T18:53:27.813

Best would be to start a daily cron job that stores the number of the data points for every day. So you can every day count the number between let's say 24.00.00 to 23.59.59.

If you want to count them on the fly you might have slow requests on huge data amounts, since the grouping query cannot use table index.

But maybe you can add a new table column where you store just the date additionally to the timestamp.

SQL Statement to get daily totals

2 Answers2