Search code examples
sqlsql-servert-sqlpivot

Sum results from a pivot to get monthly sales of produtcs

I'm writing a query to get results of monthly sales, I'm just missing a column to sum the total sales of all months.

https://dbfiddle.uk/oIclJV3u

Query:

create table sales(
    itemcode INT, name varchar(255), amount decimal(18,2), salesdate date);
insert into sales (itemcode, name, amount, salesdate)
values
(01, 'product1', 01, '2023-10-01'),
(01, 'product1', 5, '2023-11-03'),
(02, 'product1', 10, '2023-11-10')

SELECT * FROM (SELECT YEAR(SalesDate) [YEAR], DATENAME(MONTH, SalesDate) [Month], COUNT(1) [Amount Count], itemcode, Name FROM Sales
GROUP BY YEAR(SalesDate), DATENAME(MONTH, SalesDate), itemcode, Name) AS MonthlyDates
PIVOT (SUM([Amount COUNT])
FOR MONTH IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) as MNAMEPIVOT

Results in this:

year itemcode Name January February March April May June July August September October November December
2023 01 product1 NULL 10 5 1 NULL 10 2 5 10 9 10 1
2023 02 product2 5 100 NULL 65 10 32 NULL 8 89 6 46 2
2023 03 product3 6 NULL NULL NULL NULL 20 NULL 12 2 NULL NULL NULL
2023 04 product4 NULL NULL NULL NULL NULL NULL 6 NULL NULL NULL NULL NULL
2023 05 product5 10 2 5 6 10 5 41 12 1 NULL NULL NULL

Desired result:

year itemcode Name January February March April May June July August September October November December Total Sales
2023 01 product1 NULL 10 5 1 NULL 10 2 5 10 9 10 1 91
2023 02 product2 5 100 NULL 65 10 32 NULL 8 89 6 46 2 454
2023 03 product3 6 NULL NULL NULL NULL 20 NULL 12 2 NULL NULL NULL 40
2023 04 product4 NULL NULL NULL NULL NULL NULL 6 NULL NULL NULL NULL NULL 6
2023 05 product5 10 2 5 6 10 5 41 12 1 NULL NULL NULL 92
Solution

  • This is not the most elegant way of doing it and to be honest this kind of things should really be done in the presentation layer (e.g. in your report) but anyway..

    You can just UNION the year totals in your subquery can fix the name as 'Total' then just add this new month to the pivot expression.

    drop table if exists #sales
create table #sales(
  itemcode INT, name varchar(255), amount decimal(18,2), salesdate date);
insert into #sales (itemcode, name, amount, salesdate) values (01, 'product1', 01, '2023-10-01'),
(01, 'product1', 5, '2023-11-03'),
(02, 'product1', 10, '2023-11-10')

SELECT * 
  FROM (
       SELECT YEAR(SalesDate) [YEAR], 
              DATENAME(MONTH, SalesDate) [Month], 
              COUNT(1) [Amount Count], 
              itemcode, 
              Name 
       FROM #Sales
       GROUP BY YEAR(SalesDate), DATENAME(MONTH, SalesDate), itemcode, Name
       UNION ALL 
       SELECT YEAR(SalesDate) [YEAR], 
              'Total' [Month], 
              COUNT(1) [Amount Count], 
              itemcode, 
              Name 
       FROM #Sales
       GROUP BY YEAR(SalesDate), itemcode, Name  
       ) AS MonthlyDates
  PIVOT (SUM([Amount Count])
   FOR MONTH IN ([January],[February],[March],[April],[May],
      [June],[July],[August],[September],[October],[November],
      [December],
      [Total])
  ) as MNAMEPIVOT

    Here's the result

    enter image description here