how do I write a script to change the discount in the Order Details table

Question

In Northwinds how do I write a script to change the discount in the Order Details table on all orders that have an ordered quantity of more than 50 and to show the greatest discount given.

In the northwinds table, do I use MAX or SUM? It's suppose to affect 159 rows. I have this but keep getting an error.

SELECT OrderID, ProductID,UnitPrice,Quantity,
MAX (Discount)
FROM [Order Details]

Answer 1

The reason for the problem is that the database doesnt know how to show you OrderID, ProductID,UnitPrice,Quantity and still give you a max value of Discount.

When you use aggregate functions, it requires that you group by certain fields.

For example:

SELECT OrderID, ProductID,UnitPrice,Quantity, MAX (Discount)
FROM [Order Details]
group by  OrderID, ProductID,UnitPrice,Quantity

In order to get an order quantity of more than 50 you'd want to use the HAVING keyword.

SELECT OrderID, ProductID,UnitPrice,Quantity,Discount
FROM [Order Details]
having Quantity > 50