A HAVING clause filters groups after a query calculates aggregates. WHERE filters individual rows; HAVING filters grouped results, making it useful for questions such as which promotion codes were used at least three times. section 12.3
Find frequently used promotion codes
Group orders by PromotionCode, count each group, and keep only counts of three or more. The query returns the promotion codes meeting that threshold: SELECT p.PromotionCode, COUNT(oh.PromotionID) AS OrdersWithPromotionCode FROM orderheader oh INNER JOIN promotion p ON oh.PromotionID = p.PromotionID GROUP BY p.PromotionCode HAVING COUNT(oh.PromotionID) >= 3 ORDER BY p.PromotionCode; The JOIN supplies matching promotion records, GROUP BY creates one group per code, HAVING removes groups below the threshold, and ORDER BY sorts the remaining results. section 12.3
SET @MinimumQuantitySold = 10;
SELECT
t.TitleName,
SUM(oi.Quantity) AS TotalQuantitySold
FROM orderitem oi
INNER JOIN title t
ON oi.TitleID = t.TitleID
GROUP BY t.TitleName
HAVING SUM(oi.Quantity) >= @MinimumQuantitySold;
Change a variable instead of rewriting the HAVING condition to find titles whose summed quantity reaches a selected sales threshold. section 13.2
Use HAVING when the condition concerns an aggregate:
- Filter counts, sums, or other grouped calculations, such as titles with at least 10 copies sold. section 13.2
- Understand the simplified logical sequence: FROM and JOINs, WHERE, SELECT, GROUP BY, HAVING, then ORDER BY. section 12.4
- Avoid relying on SELECT aliases in HAVING when portability matters; MySQL permits them, but other database systems may differ. section 12.3 section 12.4
