Answer to Question #138461 in Databases | SQL | Oracle | MS Access for Dave

Question

*Northwind Trader (SQL)* Using the OrderDetails table, list the ProductID, the total number of orders that product was included in, and the max discount amount on any order of that product. Provide appropriate column names for the number of orders and max discount amount. Only include products where the max discount amount for that product was greater than $1,000.

Answer 1

SELECT MAX(ValueOfOrders) ValueOfOrders
FROM
(
select 
    sum(unitprice * quantity * 1+Discount) as ValueOfOrders
from 
    OrderDetails od 
join 
    orders o on od.OrderID = o.OrderID 
group by 
    CustomerID
) T