Calculate SUM of column total for each group in SQL Server

Mehram
 
on Jan 15, 2022 12:37 PM
537 Views

Sir,

I have a data like this in a table

Product      Product_Rate     Ex_rate

Aaa               100                           99

Aaa                50                             98

Need to show data in one line product wise with Sum Value Properly.

Product          tot_qty           tot_amt (rate*ex_rate)

A                     150                  14800

Select Sum(Rate * ex_Rate) from abc
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 18, 2022 07:29 AM
on Jan 19, 2022 12:44 AM

Use SUM function with Group By clause.

CREATE TABLE #Products(Product VARCHAR(20),Product_Rate INT,Ex_rate INT)
INSERT INTO #Products VALUES('Aaa',100,99),('Aaa',50,98)

SELECT Product,SUM(Product_Rate) tot_qty,SUM(Product_Rate)*MAX(ex_Rate) tot_amt 
FROM #Products 
GROUP BY Product

DROP TABLE #Products

Output

Product  tot_qty   tot_amt

     Aaa      150        14850