Group by Category
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 13 of 22.
GROUP BY partitions rows by equal key values before calculating aggregates. Select a grouping key together with summaries. A selected ordinary column must be grouped or otherwise be valid under PostgreSQL grouping rules.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT category, COUNT(*) AS product_count FROM products GROUP BY category ORDER BY category;Each category produces one row and has two products in the sample. GROUP BY changes the result grain from one row per product to one row per category. ORDER BY separately controls display order.
Group by the keys that define one output row; aggregate the values within each group.
Challenge
MediumReturn category and total_stock for each category. Sum stock and order by category.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
3Summarize Rows
Count Rows and ValuesTotals and AveragesGroup by CategoryFilter Groups with HAVINGCategory Restock BudgetPractice on your own: PostgreSQL playground