Sales report in SQL Server 2008 merging same year into one?

Question

I have the following query to display sales report in each year and month

SELECT 
    YEAR(orderDate) as SalesYear,
         MONTH(orderDate) as SalesMonth,
         SUM(Price) AS TotalSales
    FROM Sales
GROUP BY YEAR(orderDate),MONTH(orderDate)
ORDER BY YEAR(orderDate), MONTH(orderDate)

output

2013    2   350.00
2013    5   350.00
2014    8   30.00
2014    11  30.00
2015    1   350.00
2015    8   120.00

But I need? output like:

    2013    2   700.00  
    2014    2   60.00
    2015    2   470.00

Note: The month part should be the total number of months in each year.

Any help? Thanks in Advance.

Mark · Answer 1 · 2015-08-20T14:20:34.243

0

Looking at your output from your first query you need something like

  SELECT YEAR, Count(Month), Sum(TotalSales)
    FROM Sales
    GROUP BY YEAR

so

    SELECT 
    YEAR(orderDate) as SalesYear,
         Count(MONTH(orderDate)) as SalesMonthCount,
         SUM(Price) AS TotalSales
    FROM Sales
    GROUP BY YEAR(orderDate)
    ORDER BY YEAR(orderDate)

edited Aug 20 '15 at 14:20

answered Aug 20 '15 at 14:12

Mark

1,544
1
14
26

you accidentally put a comma in your group by. – Holmes IV Aug 20 '15 at 14:18
Thanks, I must have fat fingers today. – Mark Aug 20 '15 at 14:20

score 0 · Answer 2 · answered Aug 20 '15 at 14:13

0

Try:

SELECT 
      YEAR(orderDate) as SalesYear,
      count(MONTH(orderDate)) as SalesMonth,
      SUM(Price) AS TotalSales
FROM Sales
GROUP BY YEAR(orderDate)
ORDER BY YEAR(orderDate)

answered Aug 20 '15 at 14:13

Praveen

8,945
4
31
49

score 0 · Answer 3 · answered Aug 20 '15 at 14:16

This is a fairly simple thing to do. Since you want the Count of months, just do that instead. The distinct is key for the month count unless you are just trying to count total orders, in which case you should just count Price and save processing time.

SELECT 
    YEAR(orderDate) as SalesYear,
         Count(Distinct Month(orderDate)) as SalesMonth,
         SUM(Price) AS TotalSales
    FROM Sales
GROUP BY YEAR(orderDate)
ORDER BY YEAR(orderDate)

score 0 · Accepted Answer · answered Aug 20 '15 at 14:29

create table  sales (orderdate date, price money);

insert into sales values
(N'2013-02-01', 350.00), 
(N'2013-05-01', 350.00),
(N'2014-08-01', 30.00),
(N'2014-11-01', 30.00),
(N'2015-01-01', 350.00),
(N'2015-08-01', 120.00);

Alternatively, you could also use window functions SUM() OVER and COUNT() OVER to do this:

SELECT distinct
      YEAR(orderDate) as SalesYear,
      count(MONTH(orderDate)) OVER (PARTITION BY YEAR(OrderDate))  as SalesMonth,
      SUM(Price) OVER (PARTITION BY YEAR(OrderDate)) AS TotalSales
FROM Sales
ORDER BY YEAR(orderDate);

Result:

+-----------+------------+------------+
| SalesYear | SalesMonth | TotalSales |
+-----------+------------+------------+
|      2013 |          2 |        700 |
|      2014 |          2 |         60 |
|      2015 |          2 |        470 |
+-----------+------------+------------+

Demo

I am very interested in your solution with the concept Partition by. Now I have searched for it and understood. But what is the 'N' which you put while inserting values to sales table? thanks again! — Nur Selam, Aug 21 '15 at 06:44
@NurSelam Excellent! I am glad that you were interested in my solution. The `N` is just a way of declaring the date string as `nvarchar` instead of `varchar`. Here is a good explanation on this: http://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements — FutbolFan, Aug 21 '15 at 12:25

Sales report in SQL Server 2008 merging same year into one?

4 Answers4