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

select, sql, sql-server, sum

Solution

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

Problem

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?

Original source