CODEWITHSUNDEEP
sqlsql-server
Ayman
Ayman

Reputation: 51

Getting total of sum using subquery

The below query returns the following Output.

Output

BrandName   Amount
brand1      314.00
brand1      24.00
brand2      75.00
brand2      48.00

The desired Output is getting the total amount for each brand like below

BrandName   Amount
brand1      338.00
brand2      123.00

Query

SELECT Brands.BrandName,
  SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge - ((SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge) * Orders.Discount / 100) AS Amount

FROM dbo.Orders

INNER JOIN dbo.Outlets
  ON dbo.Orders.OutletID = dbo.Outlets.OutletID


INNER JOIN dbo.OrderDetails
  ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID
INNER JOIN dbo.Brands
  ON dbo.Brands.BrandID = dbo.Outlets.BrandID

GROUP BY 
Orders.OrderID,
dbo.Orders.DeliveryCharge,
Orders.Discount,
Brands.BrandName

Upvotes: 1

Views: 64

Answers (2)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

do it Like this:

SELECT grp.BrandName,sum(Amount)as Total_Amount from
(SELECT Brands.BrandName,
  SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge - ((SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge) * Orders.Discount / 100) AS Amount

FROM dbo.Orders

INNER JOIN dbo.Outlets
  ON dbo.Orders.OutletID = dbo.Outlets.OutletID


INNER JOIN dbo.OrderDetails
  ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID
INNER JOIN dbo.Brands
  ON dbo.Brands.BrandID = dbo.Outlets.BrandID

GROUP BY 
Orders.OrderID,
dbo.Orders.DeliveryCharge,
Orders.Discount,
Brands.BrandName) as grp
group by BrandName

Upvotes: 0

Akash KC
Akash KC

Reputation: 16310

You can use Common Table Expression for your scenairo 

With MyGroupCte as 
(
SELECT Brands.BrandName,
  SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge - ((SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge) * Orders.Discount / 100) AS Amount

FROM dbo.Orders

INNER JOIN dbo.Outlets
  ON dbo.Orders.OutletID = dbo.Outlets.OutletID


INNER JOIN dbo.OrderDetails
  ON dbo.OrderDetails.OrderID = dbo.Orders.OrderID
INNER JOIN dbo.Brands
  ON dbo.Brands.BrandID = dbo.Outlets.BrandID

GROUP BY 
Orders.OrderID,
dbo.Orders.DeliveryCharge,
Orders.Discount,
Brands.BrandName
)
SELECT BrandName, SUM(Amount) FROM MyGroupCte 
GROUP BY BrandName;

Upvotes: 1

Related Questions