GROUP BY Basics

Objectives

By the end of this lesson, you should be able to:

• Group rows by a shared column value with GROUP BY
• Combine GROUP BY with aggregate functions to summarize each group
• Explain why a non-aggregated, non-grouped column can’t appear in the SELECT list

💡 Why this matters: Module 7’s aggregate functions collapsed an entire table into one summary row. GROUP BY collapses it into several summary rows instead, one per department, one per region, one per whatever column defines the groups. This is how “average salary” becomes “average salary, per department.”

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier modules.

GROUP BY with COUNT

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

 department  | employee_count
--------------+-----------------
 Engineering |               3
 Marketing   |               2
 Sales       |               2
             |               1

GROUP BY department splits the 8 rows of employees into groups, one per distinct department value, including NULL (Devon, with no department, forms its own group of 1). COUNT(*) then runs separately within each group, instead of once across the whole table.

GROUP BY with Other Aggregates

SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY department;

 department  | avg_salary
--------------+------------
 Engineering |   86000.00
 Marketing   |   69000.00
 Sales       |   62000.00
             |   58000.00

Any aggregate function from Module 7, SUM(), AVG(), MIN(), MAX(), COUNT(), works the same way with GROUP BY: applied separately to each group rather than the whole table.

The Rule: Every Column Must Be Grouped or Aggregated

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

ERROR:  column "employees.first_name" must appear in the GROUP BY clause or be used in an aggregate function

This fails, and it should. Once employees is collapsed into 4 department groups, first_name no longer has one meaningful value per group, Engineering alone has Erin, Priya, and Taylor. PostgreSQL refuses to guess which one to show, every column in the SELECT list has to be either part of the GROUP BY clause (like department) or wrapped in an aggregate function (like COUNT(*), or STRING_AGG(first_name, ', ') if the goal really was to list every name).

Try It

1. Write a query showing each department and the total (SUM) salary paid in that department.
2. Write a query showing each department and the highest (MAX) salary in that department.
3. Run SELECT department, salary FROM employees GROUP BY department; and read the resulting error carefully, then fix it so it runs (hint: either group by salary too, or aggregate it).
4. Explain, in your own words, why NULL department values form their own group instead of being dropped from the result entirely.

Recap

• GROUP BY column splits rows into groups sharing the same value in that column, including a group for NULL.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) then run separately within each group.
• Every column in the SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function, PostgreSQL won’t guess which row’s value to show for an ungrouped column.

Next lesson: HAVING, filtering groups themselves after they’ve been formed.