CODEWITHSUNDEEP
sql
allendks45
allendks45

Reputation: 341

How do i aggregate the order amount for the products into one row

Using the adventureworks database i am wanting to return the total amount ordered for each product. Came up with 2-queries however confused how to aggregate the total amount into one row for each product. The first query returns multiple orders of each product id with amounts. Added a 'rollup' clause but now NULL values are returned. is the rollup the correct function to use here and how do i get the total amount of each product ordered into one result row? am i over complicating this....thanks!

first query:

select p.productid, p.name, sum(od.unitprice - od.unitpricediscount) *  
       od.orderqty as total_amount
from Production.product p
join sales.SalesOrderDetail od on p.ProductID = od.ProductID
group by p.productid, p.name, od.UnitPrice, od.UnitPriceDiscount,   
od.OrderQty
order by p.productid;

Sample of Return:

707 Sport-100 Helmet, Red   93.573
707 Sport-100 Helmet, Red   77.9775
707 Sport-100 Helmet, Red   150.4989
707 Sport-100 Helmet, Red   200.6652
707 Sport-100 Helmet, Red   313.7231
707 Sport-100 Helmet, Red   3754.689
707 Sport-100 Helmet, Red   1986.8716
707 Sport-100 Helmet, Red   1469.58

Second Query:

select p.productid, p.name, sum(od.unitprice - od.unitpricediscount) * 
od.orderqty as total_amount
from Production.product p
join sales.SalesOrderDetail od on p.ProductID = od.ProductID
group by p.productid, rollup(p.name), od.UnitPrice, od.UnitPriceDiscount,  
od.OrderQty
order by p.productid;

Example return set:

707 NULL    187.146
707 NULL    124.764
707 NULL    249.528
707 NULL    167.221
707 NULL    200.6652
707 NULL    313.7231
707 NULL    383.89
707 NULL    1635.1065

Upvotes: 0

Views: 145

Answers (1)

Jeremy Real
Jeremy Real

Reputation: 766

The problem is your group by contains the price and discount, so no summary takes place. Try this:

select p.productid, p.name, sum(od.unitprice - 
od.unitpricediscount) *  
       od.orderqty as total_amount
from Production.product p
join sales.SalesOrderDetail od on p.ProductID = 
od.ProductID
group by p.productid, p.name
order by p.productid;

Upvotes: 1

Related Questions