CODEWITHSUNDEEP
mysqlsqlselect
Peter
Peter

Reputation: 1541

MySQL : sum of every day

I have a table as follow:

datetime (datetime)  count (int)
2012-12-27 09:22:15    5
2012-12-27 18:20:15    4
2012-12-27 23:19:15    3
2012-12-26 13:45:15    8
2012-12-26 04:56:15    7
2012-12-25 01:50:15    2
2012-12-25 12:02:15    1

In MySQL, how do I query if I wish to return sum(count) for everyday and result like below:

2012-12-27    12
2012-12-26    15
2012-12-25    3

Upvotes: 13

Views: 18075

Answers (3)

Nipun Jain
Nipun Jain

Reputation: 601

Try the following to include the sum:

SELECT    DATE(datetime) day, 
SUM(`count`) Count_for_day 
FROM      tableName
GROUP BY  DATE(datetime)

Upvotes: 1

Jirilmon
Jirilmon

Reputation: 1944

Here you go:

SELECT SUM(`count`) as date_count 
FROM table_name 
GROUP BY date_format( `datetime`, '%Y-%m-%d');

Note: If possible, avoid the usage of Keywords (count) as column names.

Upvotes: 4

John Woo
John Woo

Reputation: 263713

you need to use DATE function around the columnName DateTime.

SELECT    DATE(datetime) as DATE, SUM(`count`) totalCOunt
FROM      tableName
GROUP BY  DATE(datetime)

Upvotes: 33

Related Questions