SQL query to group by day

mrblah picture mrblah · Nov 1, 2009 · Viewed 190.9k times · Source

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.

Answer

Anwar Chandra picture Anwar Chandra · Nov 1, 2009

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))