How to group sales by month with SQL Server
How to break down sales by month when using Microsoft SQLServer.
- You will need to get the year and the month out of the date.
- You will need to group by the month
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- SELECT sales_date, sales_qty, sales_price,emp_short FROM dbo.sales;
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-01-06 12:01:01.000 7.00 11.75 000190 2006-02-06 13:01:01.000 6.00 11.75 000190 2006-03-06 14:01:01.000 5.00 11.75 000110 2006-04-06 15:01:01.000 4.00 11.75 000030 2006-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000190 2006-07-06 19:01:01.000 1.00 11.75 000110 2004-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-01-06 12:01:01.000 7.00 11.75 000310 2006-02-06 13:01:01.000 6.00 11.75 000110 2005-03-06 14:01:01.000 5.00 11.75 000190 2006-04-06 15:01:01.000 4.00 11.75 000030 2006-05-06 16:01:01.000 3.00 11.75 000110 2007-06-06 17:01:01.000 2.00 11.75 000010 2006-07-06 19:01:01.000 1.00 11.75 000110 2005-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-01-06 12:01:01.000 7.00 11.75 000190 2004-02-06 13:01:01.000 6.00 11.75 000030 2004-03-06 14:01:01.000 5.00 11.75 000110 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-04-06 15:01:01.000 4.00 11.75 000010 2006-05-06 16:01:01.000 3.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000110 2006-07-06 19:01:01.000 1.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-11-06 10:01:01.000 9.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 2006-05-06 16:01:01.000 3.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000190 2006-07-06 19:01:01.000 1.00 11.75 000010 2005-10-06 10:01:01.000 10.00 11.75 000310 2007-11-06 10:01:01.000 9.00 11.75 000030 (45 row(s) affected)
Wrong way of grouping by month in SQL Server
- SELECT MONTH(sales_date) AS 'Month', SUM(sales_qty) AS "Units sold"
- FROM dbo.sales
- GROUP BY MONTH(sales_date)
- ORDER BY MONTH(sales_date);
Month Units sold ----------- --------------------------------------- 1 28.00 2 18.00 3 20.00 4 16.00 5 15.00 6 10.00 7 5.00 10 60.00 11 45.00 12 32.00 (10 row(s) affected)
- These numbers are wrong because October: month 10 includes the years 2004, 2005 and 2006.
- SQL server need to be told to summarize by Year and Month.
Right way of grouping by month in SQL Server
- SELECT YEAR(sales_date) AS 'Year', MONTH(sales_date) AS 'Month', SUM(sales_qty) AS "Units sold"
- FROM dbo.sales
- GROUP BY YEAR(sales_date), MONTH(sales_date)
- ORDER BY YEAR(sales_date), MONTH(sales_date);
Year Month Units sold ----------- ----------- --------------------------------------- 2004 2 6.00 2004 3 5.00 2004 10 10.00 2005 3 5.00 2005 4 4.00 2005 5 3.00 2005 10 40.00 2005 11 36.00 2005 12 32.00 2006 1 28.00 2006 2 12.00 2006 3 10.00 2006 4 12.00 2006 5 12.00 2006 6 8.00 2006 7 5.00 2006 10 10.00 2007 6 2.00 2007 11 9.00 (19 row(s) affected)
- Microsoft SQL Server, like most modern SQL Servers, have very rich date extracts, calculations and formatting functions.
- See How to extract date portions from the dates