4.3 — Multi-level Grouping
What You'll Learn
- How to group by more than one column in SQL
- How this compares to nested row labels in Excel PivotTables
Grouping by Multiple Columns
Here is the sales data we'll use in our examples:
| Product | Amount | SaleDate |
|---|---|---|
| Apples | 50 | 2024-05-01 |
| Apples | 60 | 2024-05-01 |
| Apples | 30 | 2024-05-02 |
| Apples | 20 | 2024-05-02 |
| Oranges | 40 | 2024-05-01 |
| Oranges | 40 | 2024-05-01 |
| Oranges | 25 | 2024-05-03 |
| Oranges | 15 | 2024-05-03 |
In Excel, you can add multiple row labels to a PivotTable. In SQL, just list more columns in GROUP BY.
Example: Total sales by product and date
sql
SELECT Product, SaleDate, SUM(Amount) AS Total_Sales, AVG(Amount) AS AVG_Sales
FROM sales
GROUP BY Product, SaleDate
ORDER BY Product, SaleDate;- Each combination of Product and SaleDate gets its own row.
Key Points
- List multiple columns in
GROUP BYfor detailed summaries. - The order of columns in
GROUP BYaffects the grouping.
Next Steps
Next, you’ll learn how to filter your grouped results using the HAVING clause.