SQL Group By

Why Group Rows Together

Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX normally reduce an entire table to a single number. GROUP BY changes that behaviour: instead of one answer for the whole table, you get one answer per group. You choose the groups by naming one or more columns, and every set of rows that shares the same values in those columns becomes a single output row.

For example, if an employees table stores a department for each person, grouping by department lets you count how many employees work in each one, or calculate the average salary within each department, all in a single query.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Reading that query left to right: the rows are gathered into buckets by department, COUNT(*) counts the rows in each bucket, and the result has one line per department with its headcount beside it.

The Rule About Selected Columns

When you use GROUP BY, every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. This makes sense: if you group by department, each output row represents many employees, so a plain first_name column would have no single value to show. The database only knows how to display a value per group if it is a grouping column or an aggregate.

• Grouping columns: listed after GROUP BY and shown as-is.
• Aggregated columns: passed through COUNT, SUM, AVG, MIN, or MAX.
• Any other column produces an error in standard SQL.

SELECT department,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

Grouping by More Than One Column

You can list several columns after GROUP BY. The database then forms a group for each unique combination of those column values. Grouping by department and city, for instance, gives one row for every department-and-city pairing that exists in the data.

SELECT city, department, COUNT(*) AS headcount
FROM employees
GROUP BY city, department
ORDER BY city, department;