CODEWITHSUNDEEP
sqlsql-serversql-server-2005group-by
mrblah
mrblah

Reputation: 103647

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.

Upvotes: 188

Views: 303156

Answers (9)

illuminato
illuminato

Reputation: 1257

The simplest and intuitive solution for MySQL is:

GROUP BY day(datefield)

Upvotes: 0

Andomar
Andomar

Reputation: 238256

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)

Upvotes: 171

Jon Bright
Jon Bright

Reputation: 13748

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)

Upvotes: 66

codeisrun
codeisrun

Reputation: 113

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

Upvotes: -6

Anwar Chandra
Anwar Chandra

Reputation: 8648

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

Upvotes: 216

Paco Valdez
Paco Valdez

Reputation: 1975

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

Upvotes: 10

marc_s
marc_s

Reputation: 755197

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

Upvotes: 7

yopefonic
yopefonic

Reputation: 532

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

Upvotes: 8

Thorsten
Thorsten

Reputation: 13181

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.

Upvotes: 3

Related Questions