Calculate Sum using GroupBy and Pivot function in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

Hi! I have three table. But I want separate sum sales by food and person.

CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10))
INSERT INTO #FOOD(Id, NAME)
SELECT 1,'apple'
UNION ALL
SELECT 2,'orange'
UNION ALL
SELECT 3,'tomato'

CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10))
INSERT INTO #PEOPLE(Id, NAME)
SELECT 1,'Imomdod'
UNION ALL
SELECT 2,'Muhammad'
UNION ALL
SELECT 3,'Alisher'
 
CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float)
INSERT INTO #SALES(Id, IdPer, IDFOOD, price)
SELECT 1,2,1, 3
UNION ALL
SELECT 2,3,1, 5
UNION ALL
SELECT 3,1,3, 11
UNION ALL
SELECT 4,3,2, 2
UNION ALL
SELECT 5,2,1, 5
UNION ALL
SELECT 6,1,2, 8
UNION ALL
SELECT 7,2,3, 4
UNION ALL
SELECT 8,3,2, 10
UNION ALL
SELECT 9,2,3, 15
UNION ALL
SELECT 10,1,2, 18
UNION ALL
SELECT 11,3,3, 13
UNION ALL
SELECT 12,1,3, 19
select * from #SALES order by IdPer
DROP TABLE #SALES
DROP TABLE #FOOD
DROP TABLE #PEOPLE

Output result:

Id

Name

Apple

Orange

Tomato

Summa

1

Imomdod

 

30

26

56

2

Muhammad

8

 

19

27

3

Alisher

5

12

13

30

Posted one year ago Modified on one year ago

Hi RPA,

Check the below test query.

SQL

CREATE TABLE #FOOD(Id INT, NAME VARCHAR(10))
INSERT INTO #FOOD(Id, NAME)
SELECT 1,'apple' UNION ALL SELECT 2,'orange' UNION ALL SELECT 3,'tomato'
 
CREATE TABLE #PEOPLE(Id INT, NAME VARCHAR(10))
INSERT INTO #PEOPLE(Id, NAME)
SELECT 1,'Imomdod' UNION ALL SELECT 2,'Muhammad' UNION ALL SELECT 3,'Alisher'
  
CREATE TABLE #SALES(Id INT, IdPer INT, IDFOOD INT, price float)
INSERT INTO #SALES(Id, IdPer, IDFOOD, price)
SELECT 1,2,1, 3 UNION ALL SELECT 2,3,1, 5 UNION ALL SELECT 3,1,3, 11 UNION ALL
SELECT 4,3,2, 2 UNION ALL SELECT 5,2,1, 5 UNION ALL SELECT 6,1,2, 8 UNION ALL
SELECT 7,2,3, 4 UNION ALL SELECT 8,3,2, 10 UNION ALL SELECT 9,2,3, 15 UNION ALL
SELECT 10,1,2, 18 UNION ALL SELECT 11,3,3, 13 UNION ALL SELECT 12,1,3, 19

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(NAME) FROM (SELECT DISTINCT [NAME] FROM #FOOD) AS [NAME]
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')

SET @DynamicPivotQuery = 'SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) Id
                         ,PersonName,'+@ColumnName+'
                         ,SUM('+@ColumnForSum+') Summa 
                         FROM (
                            SELECT SUM(s.Price) Price,p.NAME as PersonName,f.NAME as FoodName
                            from #SALES s
                            INNER JOIN #PEOPLE p ON s.IdPer = p.Id
                            INNER JOIN #FOOD f ON s.IDFOOD = f.Id
                            GROUP BY p.Name,f.Name)t
                            pivot
                            (
                                MAX([Price]) FOR FoodName IN ('+@ColumnName+')
                            ) piv GROUP BY PersonName,'+@ColumnName+''
EXEC (@DynamicPivotQuery)

DROP TABLE #SALES
DROP TABLE #FOOD
DROP TABLE #PEOPLE

Output

Id PersonName apple orange tomato Summa
1 Alisher 5 12 13 30
2 Imomdod NULL 26 30 56
3 Muhammad 8 NULL 19 27