SQL query to group by day

I want to list all sales, and group the sum by day.

Sales (saleID INT, amount INT, created DATETIME) 

NOTE: I am using SQL Server 2005.

8 Answers

if you're using SQL Server,

dateadd(DAY,0, datediff(day,0, created)) will return the day created

for example, if the sale created on '2009-11-02 06:12:55.000', dateadd(DAY,0, datediff(day,0, created)) return '2009-11-02 00:00:00.000'

select sum(amount) as total, dateadd(DAY,0, datediff(day,0, created)) as created from sales group by dateadd(DAY,0, datediff(day,0, created)) 

For SQL Server:

GROUP BY datepart(year, datefield), datepart(month, datefield), datepart(day, datefield) 

or faster (from Q8-Coder):

GROUP BY dateadd(DAY, 0, datediff(day, 0, created)) 

For MySQL:

GROUP BY year(datefield), month(datefield), day(datefield) 

or better (from Jon Bright):

GROUP BY date(datefield) 

For Oracle:

GROUP BY to_char(datefield, 'yyyy-mm-dd') 

or faster (from IronGoofy):

GROUP BY trunc(created); 

For Informix (by Jonathan Leffler):

GROUP BY date_column GROUP BY EXTEND(datetime_column, YEAR TO DAY) 

If you're using MySQL:

SELECT DATE(created) AS saledate, SUM(amount) FROM Sales GROUP BY saledate 

If you're using MS SQL 2008:

SELECT CAST(created AS date) AS saledate, SUM(amount) FROM Sales GROUP BY CAST(created AS date) 

actually this depends on what DBMS you are using but in regular SQL convert(varchar,DateColumn,101) will change the DATETIME format to date (one day)

so:

SELECT sum(amount) FROM sales GROUP BY convert(varchar,created,101) 

the magix number 101 is what date format it is converted to

For PostgreSQL:

GROUP BY to_char(timestampfield, 'yyyy-mm-dd') 

or using cast:

GROUP BY timestampfield::date 

if you want speed, use the second option and add an index:

CREATE INDEX tablename_timestampfield_date_idx ON tablename(date(timestampfield)); 

If you're using SQL Server, you could add three calculated fields to your table:

Sales (saleID INT, amount INT, created DATETIME) ALTER TABLE dbo.Sales ADD SaleYear AS YEAR(Created) PERSISTED ALTER TABLE dbo.Sales ADD SaleMonth AS MONTH(Created) PERSISTED ALTER TABLE dbo.Sales ADD SaleDay AS DAY(Created) PERSISTED 

and now you could easily group by, order by etc. by day, month or year of the sale:

SELECT SaleDay, SUM(Amount) FROM dbo.Sales GROUP BY SaleDay 

Those calculated fields will always be kept up to date (when your "Created" date changes), they're part of your table, they can be used just like regular fields, and can even be indexed (if they're "PERSISTED") - great feature that's totally underused, IMHO.

Marc

For oracle you can

group by trunc(created); 

as this truncates the created datetime to the previous midnight.

Another option is to

group by to_char(created, 'DD.MM.YYYY'); 

which achieves the same result, but may be slower as it requires a type conversion.

use linq

from c in Customers group c by DbFunctions.TruncateTime(c.CreateTime) into date orderby date.Key descending select new { Value = date.Count().ToString(), Name = date.Key.ToString().Substring(0, 10) }