Aggregations with GROUP BY and HAVING
Understand how to apply SQL aggregations with GROUP BY to summarize data by category and use HAVING to filter groups. Learn to distinguish filtering individual rows with WHERE from filtering aggregated groups, enabling you to create clear, accurate category reports for business analysis.
We'll cover the following...
An order_items result can list every product line that customers bought, yet that detail cannot answer which category generates the most revenue. A category report needs fewer rows, with one row for Electronics, one for Home, and one for Books.
Aggregation collapses many input rows into fewer output rows. COUNT(*) counts rows, SUM(quantity) adds quantities, and AVG(quantity * unit_price) calculates an average from several line-item amounts. GROUP BY decides which rows belong together before SQL calculates those values.
For a category manager, the question is concrete. Which categories produce the most revenue, and which categories have an unusually low average line revenue? Revenue for one order item is computed from its quantity and unit price.
The key decision comes before we write the query. WHERE removes individual order-item rows before SQL builds groups. HAVING removes whole groups after SQL calculates their summary values. The clauses can look similar, but they answer different business questions.
Build a category report
Because order_items records one product line within one order, several rows can belong to the same category. The report needs products.category to place each order item into the correct category, then needs GROUP BY category to make one output row per category.
The query below produces four metrics for each category. line_item_count tells us how many order-item rows contributed. total_units counts physical units sold. total_revenue reports category revenue. ...