SQL guide · Aggregation

SQL GROUP BY: Aggregate Rows by One or More Columns

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

Browse all SQL guides

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