GROUP BY

Explanation

So far you have queried rows one at a time. But much of the value of SQL comes from summarizing data — counting how many products fall in each category, finding the average price per category, or totaling revenue per customer.

GROUP BY collapses rows that share the same value into a single group, and aggregate functions then run once per group instead of once for the whole table.

Look at the products table in the store dataset. Each row is one product, and every product belongs to a category such as Electronics or Furniture.

Syntax

SELECT column, AGGREGATE(another_column)
FROM table_name
GROUP BY column;

Every column in the SELECT list that is not wrapped in an aggregate function must appear in the GROUP BY clause.

Interactive Example

Count how many products exist in each category. Then try averaging the price per category.

Common Mistakes

• Selecting a column you forgot to group by. If you write SELECT name, COUNT(*) FROM products GROUP BY category, most databases reject it because name is not aggregated and not in the GROUP BY.
• Assuming GROUP BY sorts the output. It does not — add ORDER BY if you need a specific order.
• Using WHERE to filter groups. WHERE runs before grouping. To filter on the result of an aggregate, you need HAVING (see the next lesson).

Best Practices

• Put the same columns in GROUP BY that you selected without aggregation.
• Give aggregates clear aliases (AS total, AS avg_price) so results are readable.
• Use ORDER BY on the aggregated column when you want the biggest or smallest groups first, e.g. ORDER BY product_count DESC.

Practice Question

Using the products table, write a query that returns each category with the total stock across all products in that category, ordered from highest total stock to lowest.

Summary

GROUP BY turns many rows into summarized groups, letting aggregate functions compute one value per group. Any non-aggregated column in the SELECT list must be listed in GROUP BY. Filter before grouping with WHERE, and after grouping with HAVING.

FAQ