Examples are based on the GoGo Gadgets database

Aggregate functions (sum, count, max, min, avg) are used to create summarised GROUP BY results.

However these summarised results cannot be restricted in a WHERE clause – which is used to specify which individual records are to be included in the results.

The HAVING clause is used to filter the results from aggregate functions summarised GROUP BY.

-- display total sales for each item
SELECT Item.itemID, SUM(quantity*price) AS "Total Sales"
FROM OrderItem, Item
WHERE OrderItem.ItemID = Item.ItemID
GROUP BY Item.itemID
ORDER BY SUM(quantity*price) DESC

-- display items where total sales greater than 200
SELECT Item.itemID, SUM(quantity*price) AS "Total Sales"
FROM OrderItem, Item
WHERE OrderItem.ItemID = Item.ItemID
GROUP BY Item.itemID
HAVING SUM(quantity*price) > 200
ORDER BY SUM(quantity*price) DESC