# 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
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