Sum of each user data in SQL SERVER

Question

Name     Price  Discount  A  10     2  A  20  5  A  55  3            Total      85  11  B  5  6  B  15  9  B  10  2   Total  30  17       The above table has separate total for each user. How to calculate it in sql server and return and different dataset or a combine dataset

Khan Abrar Ahmed · Accepted Answer

you can try the below query, but if you are using the SSRS report then in the report use group total feature. DECLARE @temp AS TABLE ( NAME VARCHAR, Price INT, Discount INT ); INSERT INTO @temp VALUES ('A', 10, 2), ('A', 20, 5), ('A', 55, 3), ('B', 5, 6), ('B', 15, 10), ('B', 10, 2); WITH CTE_TOTAL AS ( SELECT Name, SUM(Price) AS Price, SUM(Discount) AS Discount FROM @temp GROUP BY NAME) SELECT CASE WHEN OrderValue =1 THEN 'Total' ELSE x.NAME END AS Item, x.Price, x.Discount, x.OrderValue FROM ( SELECT NAME,c.Price,c.Discount,1 AS OrderValue FROM CTE_TOTAL c UNION SELECT c.NAME,c.Price,c.Discount,2 AS OrderValue FROM @temp c ) x ORDER BY name

Tapan Patel · Answer

Other than accepted answer, you can try something like below simple queries    SELECT  *    INTO  #tempTable   FROM     (        SELECT   'A'   Name , 10 Price, 2 Discount  UNION         SELECT   'A'   Name , 20 Price, 5 Discount  UNION         SELECT   'A'   Name , 55 Price, 3 Discount  UNION         SELECT   'B'   Name , 5 Price, 6 Discount  UNION         SELECT   'B'   Name , 15 Price, 9 Discount  UNION         SELECT   'B'   Name , 10 Price, 2 Discount   ) ce          -- Option 1    SELECT  *  FROM  #tempTable   UNION   ALL    SELECT   Name  ,           SUM (Price) ,           SUM (Discount)   FROM  #tempTable   GROUP   BY   Name    WITH   CUBE             -- yes, this is interesting feature of SQL Server   , this is more useful when you have more columns to take care of HAVING   name   IS   NOT   NULL    ORDER   BY   Name ,Price      -- Option 2    SELECT  *  FROM  #tempTable   UNION   ALL    SELECT   Name  ,           SUM (Price) ,           SUM (Discount)   FROM  #tempTable   GROUP   BY   Name    ORDER   BY   Name ,Price

John · Answer

Thanks a lot

John · Answer

Yes in report c# but if possible i want to do it in sql

Khan Abrar Ahmed · Answer

where you want to show the data means in Reprot or some front end??

John · Answer

Name Price Discount A 10 2 A 20 5 A 55 3 B 5 6 B 15 9 B 10 2   This is my actula table  and i want to display like above table as result

Khan Abrar Ahmed · Answer

Hi, can you please expalin more, as I think you don't need to add sum in the sql use sum or total fucntions in the front end.