# SQL GROUP BY: Aggregate Rows by One or More Columns

- URL: https://betteranalyst.com/sql/group-by

SQL guide · Aggregation

The `GROUP BY` clause collapses rows that share the same values in one or more columns into a single summary row per group. You combine it with aggregate functions such as `COUNT`, `SUM`, `AVG`, `MIN` and `MAX` to answer questions like how many orders each customer placed, the total revenue per product category, or the average salary per department.

Without `GROUP BY`, an aggregate function treats the whole table (or all rows that pass the `WHERE` filter) as one group and returns a single row. With it, the database sorts or hashes the rows into buckets by the grouping columns and computes each aggregate once per bucket. The result has one row per distinct combination of the grouping columns.

Order of operations matters. `WHERE` filters individual rows before they are grouped, `GROUP BY` forms the groups, `HAVING` filters the groups afterwards, and `ORDER BY` sorts the final rows. Keeping this sequence in mind explains most of the errors people hit when they first use grouping.

## Basic syntax

```
SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
[WHERE condition]
GROUP BY column1, column2
[HAVING group_condition]
[ORDER BY column1];
```

Every column in the `SELECT` list must either appear in `GROUP BY` or be wrapped in an aggregate function. `WHERE` runs before grouping and cannot reference aggregates, while `HAVING` runs after grouping and can.

## SQL GROUP BY examples

### Orders per customer

This counts the rows in `orders` for each customer id. The result has one row per customer who has at least one order, with the number of orders in the second column.

```
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;
```

### Revenue by category

A join brings in the category, and `SUM` totals the line amounts. Aggregates can use expressions, so multiplying quantity by price happens before the sum is taken.

```
SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi
INNER JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;
```

### Grouping by several columns

Listing two columns creates a group for every distinct pair. This gives the number of employees and their average salary for each department and job title.

```
SELECT department, job_title,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, job_title;
```

### Grouping by a date part

You can group by an expression to bucket rows, such as by month. The expression in `SELECT` must match the one in `GROUP BY`, and the exact date function depends on your database.

```
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       COUNT(*) AS orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;
```

## Where SQL dialects differ

**PostgreSQL:** Allows grouping by column position (`GROUP BY 1`) and by output alias. If you group by a primary key, other columns of that same table can be selected without being listed. Also supports `ROLLUP`, `CUBE` and `GROUPING SETS`.

**MySQL:** With the default `ONLY_FULL_GROUP_BY` mode (on since 5.7), selecting a non-grouped, non-aggregated column is an error. Older setups silently returned an arbitrary value. Supports `WITH ROLLUP` after the column list.

**SQL Server:** Does not allow output aliases in `GROUP BY`, so repeat the full expression. Supports `ROLLUP`, `CUBE` and `GROUPING SETS`, and does not allow grouping by column position.

**SQLite:** Is lenient and allows bare columns in `SELECT` that are not grouped, returning a value from an arbitrary row in the group. Do not rely on this. It allows aliases and positions in `GROUP BY`.

**Oracle:** Requires every non-aggregated column in `GROUP BY` and does not allow aliases or positions there. Supports `ROLLUP`, `CUBE` and `GROUPING SETS`.

## Common mistakes

- Selecting a column that is neither grouped nor aggregated. Most databases reject it, and the ones that do not return an arbitrary value from the group.
- Using an aggregate in `WHERE`, such as `WHERE COUNT(*) > 5`. Aggregates exist only after grouping, so the condition belongs in `HAVING`.
- Grouping by a join result without noticing row multiplication. If a join repeats rows, `SUM` and `COUNT` inflate, so aggregate before joining or use `COUNT(DISTINCT ...)`.
- Forgetting that `NULL` values form their own group. Rows with a `NULL` grouping column are bundled together and show up as one row.
- Assuming groups come back in order. `GROUP BY` does not guarantee ordering, so add `ORDER BY` when order matters.

## Related SQL guides

- [SQL HAVING](https://betteranalyst.com/sql/having)
- [SQL SELECT](https://betteranalyst.com/sql/select)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL ORDER BY](https://betteranalyst.com/sql/order-by)
- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL SELECT DISTINCT](https://betteranalyst.com/sql/distinct)

[Browse all SQL guides](https://betteranalyst.com/sql)

## Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

[Try the AI SQL generator](https://betteranalyst.com/ai-sql-query-generator)