Typical Mistakes When Working with Aggregates

Now it's time to dive into the typical mistakes that pop up when using these functions. Even the most experienced SQL pros step on these rakes sometimes, and our job is to learn how to spot them and dodge them like a boss.

Ever written a query and got yelled at for something mysterious like "column must appear in the GROUP BY clause or be used in an aggregate function"? Or maybe your query result looked weird and you had no clue why? That's just the tip of the iceberg when it comes to classic aggregate function mistakes. This lecture is your survival guide for the sea of errors and confusion.

Mistake 1: Using a Non-Aggregated Column Outside GROUP BY

The Problem

You wrote a query that returns aggregated data, but along the way you added a column that's not part of the grouping and isn't wrapped in an aggregate function. For example:

SELECT department, salary, SUM(salary)
FROM employees
GROUP BY department;

PostgreSQL will instantly tell you:

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

Why does this happen?

When you use GROUP BY, PostgreSQL groups rows by the columns you specify. But if you add another column (in this case salary), PostgreSQL has no idea what you want to do with it. Do you want just one salary, the average, or something else?

How to fix it? There are two ways:

1. Make sure all non-aggregated columns are present in GROUP BY:

SELECT department, salary
FROM employees
GROUP BY department, salary;

2. Or wrap the column in an aggregate function, if that makes sense:

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

Pro tip: if PostgreSQL complains about GROUP BY, ask yourself: "Do I really need this column in the query? If yes, what exactly is its role?"

Mistake 2: Messing Up COUNT() and NULL

The problem: you want to count how many employees have specified their bonuses, and you write:

SELECT COUNT(bonus) AS bonus_count
FROM employees;

But suddenly you notice the result is lower than you expected. Why? Because COUNT(column) ignores rows where column is NULL.

The fix: if you want to count all rows, use COUNT(*):

SELECT COUNT(*) AS total_count
FROM employees;

Or specify that you only want rows where bonus is not NULL:

SELECT COUNT(bonus) AS bonus_count
FROM employees
WHERE bonus IS NOT NULL;

Hint: if you want to tell the difference between records that have NULL and a total lack of records in the table, be careful when choosing between COUNT(*) and COUNT(column).

Mistake 3: Forgetting to Filter with HAVING Instead of WHERE

The problem: you want to find departments where the average salary is over 5000. A newbie dev might write something like this:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 5000
GROUP BY department;

PostgreSQL will throw an error:

ERROR: aggregate functions are not allowed in WHERE

This happens because filtering with WHERE happens before grouping, and aggregate functions are applied only after grouping. So, the average salary AVG(salary) hasn't even been calculated yet when WHERE runs.

To fix this, use HAVING to filter aggregated data:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

Mistake 4: Filtering with WHERE and Getting Lost in Execution Order

The problem: you want to know the number of employees in departments where employees are older than 30. The query might look like this:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
WHERE age > 30;

PostgreSQL will disappoint you again:

ERROR: syntax error at or near "WHERE"

Why does this happen? The WHERE clause is always processed before GROUP BY. In this case, you just put WHERE in the wrong place.

To avoid this, switch up the order: filter the rows first, then group them.

SELECT department, COUNT(*)
FROM employees
WHERE age > 30
GROUP BY department;

Mistake 5: Using NULL with SUM(), AVG() and Other Functions

The problem: you want to find the total bonus given to employees, and you write:

SELECT SUM(bonus) AS total_bonus
FROM employees;

If bonuses aren't specified (NULL), they're simply ignored — that's the default behavior of SUM. The temptation is to "fix" this with COALESCE:

SELECT SUM(COALESCE(bonus, 0)) AS total_bonus
FROM employees;

Heads up: for SUM both queries give an identical numeric result — adding zeros doesn't change the sum. The only real difference shows up when all values in the column are NULL: SUM(bonus) → NULL, while SUM(COALESCE(bonus, 0)) → 0. Often that's exactly what you want — a guarantee of a number on the output. For AVG, however, the difference appears with any NULL: AVG(bonus) divides the sum only by non-NULL rows, while AVG(COALESCE(bonus, 0)) — by all rows (treating an absent bonus as a zero bonus).

We'll take a closer look at how COALESCE works in just a couple of lectures.

Mistake 6: Using Multiple Aggregate Functions Without Understanding Their Relationship

The problem: you want to count the total number of employees and the total salary. But you write something that returns weird results:

SELECT COUNT(salary) AS count_salary, SUM(salary) AS total_salary
FROM employees;

COUNT(salary) and SUM(salary) are fundamentally two different functions (one counts the number of non-NULL values, the other sums them up) — they shouldn't give the same result anyway. The real NULL trap is the difference between AVG(salary) and SUM(salary)/COUNT(*): AVG divides the sum by the number of non-NULL values, while SUM/COUNT(*) divides by the total number of rows. If the data has NULLs, these two expressions will produce different numbers.

Always remember that aggregate functions work independently of each other. If there are NULLs, you'll get different results. Use COALESCE or COUNT(*) to make things consistent:

SELECT COUNT(*) AS total_employees, SUM(COALESCE(salary, 0)) AS total_salary
FROM employees;

Mistake 7: Unoptimized Queries with Huge Groupings

The problem: you run a query with a ton of groupings, and it takes five hours instead of five minutes:

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

Before you run a grouping, think about whether you really need all those columns in GROUP BY. The more unique values in a group, the longer the query takes. If possible, cut down the grouping:

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

These mistakes are super common, and even the most experienced SQL devs run into them. Hopefully, now it'll be easier for you to dodge these pitfalls and write queries that are fast, correct, and clean.