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)

Update
I am using SQL Server 2005

  • Operand type clash: varchar is incompatible with varchar(50) trying to insert in encrypted database
  • Incorrect syntax near the keyword 'with'.
  • Efficient query for finding duplicate records
  • Clustered index
  • How to get number of rows updated by a query in .net
  • How to decode nvarchar to text (SQL Server 2008 R2)?
  • 8 Solutions collect form web for “Sql query to Group by day”

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

    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

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

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

    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)
    }
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.