CODEWITHSUNDEEP
sqlsql-serverselectsum
Tony_Henrich
Tony_Henrich

Reputation: 44095

How to get total sales for each product in Order Details table in Northwind database in a single statement?

How to get total sales for each product in Order Details table in Northwind database in a single statement?

I know how to do this: 

select productid,  UnitPrice * (1 - Discount) * sum(Quantity) 
  from [Order Details]
    group by ProductID,UnitPrice, Discount

I get multiple rows for each productid and then I can run another query to get one sum for each productid. Note that the discount can be different for the same productid.

I would like to do it all in a single SQL statement. How?

Upvotes: 3

Views: 13580

Answers (1)

Mureinik
Mureinik

Reputation: 311438

You could just extract your sum function to sum the price for each row individually, instead of just summing the quantity (this should be OK mathematically due to the distributive property of multiplication over addition):

SELECT   productid,  SUM(UnitPrice * (1 - Discount) * Quantity)
FROM     [Order Details]
GROUP BY ProductID

Upvotes: 3

Related Questions