Sql request to the Group for the day

Question

I want to list all sales and group the amount per day.

Sales (saleID INT, amount INT, created DATETIME) 

Update I am using SQL Server 2005

Answer 1

if you are using SQL Server,

dateadd(DAY,0, datediff(day,0, created)) created day will be returned

for example, if a 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)) 

Answer 2

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 (Jonathan Leffler):

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

Answer 3

If you are using MySQL:

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

If you are using MS SQL 2008:

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

Answer 4

If you are using SQL Server, you can add three tables to the 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 can easily group, order, etc. day, month or year of sale:

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

These calculated fields will always be updated (when your "Created" date changes), they are part of your table, they can be used as regular fields and can even be indexed (if they are "PERSISTED") - a great feature that is completely underused, IMHO.

Mark

Answer 5

in fact, it depends on which DBMS you use, but in plain SQL convert(varchar,DateColumn,101) will change the DATETIME format for today (one day)

So:

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

magic number 101 is the date format that it converts to

Answer 6

For oracle you can

 group by trunc(created); 

since it shortens the created datetime to the previous midnight.

Another variant -

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

which achieves the same result, but can be slower because it requires type conversion.

Answer 7

For PostgreSQL:

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

or using listing:

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

Answer 8

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