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

Question

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?

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