CODEWITHSUNDEEP
sqlsql-server
user6347596
user6347596

Reputation:

How to do Sum Operation in SQl Pivot Query

Here i have a simple table Name Cust With CustName,PurshaseItem,Price.I wrote a Simple PIVOT query its Pivoting the data but i wana to show sum of the amot Here i need Grand total

Pivot Query

[![select custName,

       \[Shoes\] as Shoes,
       \[Colgate\] as Colgate,
       \[Cloths\] as Cloths

FROM
(select custName,Price,PurchaseItem FROM Cust
) AS PIVOTData

PIVOT(
sum(Price) FOR PurchaseItem 
IN (Shoes,Colgate,Cloths)
)AS PIVOTING][1]][1]


custname       Shoes    Colgate       GrandTotal
xyz           12         10           22
lmn            1          2            3

Upvotes: 0

Views: 56

Answers (2)

etsa
etsa

Reputation: 5060

You can try this:

CREATE TABLE CUST (custName VARCHAR(10),
       price INT,
       PurchaseItem VARCHAR(10)
       )
       INSERT INTO CUST VALUES ('aaaa', 1,'Colgate')
       INSERT INTO CUST VALUES ('aaaa', 2,'Shoes')
       INSERT INTO CUST VALUES ('aaaa', 3,'Cloths')
       INSERT INTO CUST VALUES ('bbbb', 4,'Colgate')
       INSERT INTO CUST VALUES ('bbbb', 5,'Shoes')
       INSERT INTO CUST VALUES ('bbbb', 6,'Cloths')


select *
FROM
(select custName, SUM(Price)   AS Price ,
     CASE WHEN GROUPING(PurchaseItem)=1 THEN 'TOT_PRICE' ELSE PurchaseItem END AS PurchaseItem
  FROM Cust
  group by rollup(PurchaseItem), custName
) AS PIVOTData

PIVOT(sum(Price) FOR PurchaseItem IN (Shoes,Colgate,Cloths,TOT_PRICE)) AS PIVOTING

Output:

custName   Shoes       Colgate     Cloths      TOT_PRICE
---------- ----------- ----------- ----------- -----------
aaaa       2           1           3           6
bbbb       5           4           6           15

Upvotes: 2

Stefano Zanini
Stefano Zanini

Reputation: 5916

You can just add this to your select

coalesce([Shoes], 0) + coalesce([Colgate], 0) + coalesce([Cloths], 0) as GranTotal

The coalesce is needed to avoid weird behaviours when one of the results is null.

Upvotes: 0

Related Questions