Data aggregation combines or summarizes values so a query can analyze groups rather than only individual rows. GROUP BY divides one result set into logical groups, such as orders grouped by promotion code. section 12.2
Count orders by promotion code
To find promotion codes used in orders, join orderheader to promotion, group by PromotionCode, and count PromotionID. Add HAVING COUNT(oh.PromotionID) >= 3 to keep only codes used at least three times. WHERE filters individual rows; HAVING filters aggregated groups. section 12.3
Practical details to remember:
- GROUP BY includes NULL as a group, unlike COUNT applied without grouping, which excludes NULL values from the counted column. section 12.2.2
- Logical processing evaluates FROM, WHERE, SELECT, GROUP BY, HAVING, and ORDER BY in sequence; avoid relying on column aliases in HAVING because database products can differ. section 12.4
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;
Inspect a complete grouped query that joins promotion data, counts matching orders, and filters groups with HAVING. section 12.3
