SQL · Chapter 21 of 42

SQL GROUP BY

GROUP BY aggregates rows that share a value. Usually paired with COUNT, SUM, AVG, MIN, MAX.

Every non-aggregated column in the SELECT must appear in GROUP BY (or be an aggregate).

Syntax

SELECT col, AGG(col2) FROM table GROUP BY col;

Basics

`SELECT country, COUNT(*) FROM users GROUP BY country;` returns one row per country with a user count.

Selecting a non-aggregated column that isn't in GROUP BY works in some MySQL configs but is an error in standard SQL.

Example 1 (sql)

SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
ORDER BY users DESC;

Output

country | users
India   | 40
USA     | 25

One row per country, sorted by count.

Example 2 (sql)

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

Output

avg price per category

AVG with GROUP BY.

💡 Note: `GROUP BY 1, 2` refers to SELECT column positions — handy but fragile if you reorder columns.