SQL query to group by day

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL query to group by day

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to group-by