Common Aggregation Mistakes

Selecting a non-grouped, non-aggregated column is non-standard and can produce indeterminate values in permissive MySQL modes. HAVING without GROUP BY is valid for a single aggregate group, although WHERE is the right tool for ordinary row filtering.

Aggregation functions are extremely powerful, but beginners often make mistakes because SQL executes queries in a specific internal order. Understanding these mistakes will help you avoid errors in interviews and real-world projects.

1. Selecting Non-Grouped Columns

One of the most common SQL mistakes is selecting columns that are neither:

• part of the GROUP BY clause
• nor wrapped inside an aggregate function

Example (Wrong):

SELECT employee_name, project
FROM employees
GROUP BY project;

Why This Fails

Inside one project group, there are multiple employees.

Example:

Alpha
 ├── Alice
 ├── Bob
 ├── Carol
 └── Dave

When SQL groups by project, it creates one row per project.

Now the database becomes confused:

Which employee_name should I show for Alpha?
Alice?
Bob?
Carol?
Dave?

SQL cannot decide.

That is why the query fails.

Correct Query

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

Key Rule

Every selected column must either be:

• inside GROUP BY
• or inside an aggregate function

2. Confusing GROUP BY with ORDER BY

Many beginners think GROUP BY sorts data.

This is incorrect.

Difference

Important Concept

Even if GROUP BY sometimes appears sorted:

GROUP BY project

you should NEVER depend on that behavior.

Always use ORDER BY if sorting is required.

Correct Example

SELECT project,
       COUNT(*) AS total_employees
FROM employees
GROUP BY project
ORDER BY total_employees DESC;

3. Grouping by Primary Keys

Grouping by unique IDs is usually meaningless.

Example:

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

Why This is Useless

Primary keys are unique.

Every emp_id creates its own group.

Result:

1 → 1 row
2 → 1 row
3 → 1 row
...

COUNT will always become 1.

No real aggregation happens.

Correct Mental Model

Group using repeating values like:

• department
• city
• project
• category
• event

NOT unique IDs.

4. Using SUM() or AVG() on Text Columns

SUM and AVG only work on numeric data.

Wrong:

SELECT SUM(employee_name)
FROM employees;

Why This Fails

SQL cannot mathematically add names together.

Alice + Bob + Carol ❌

Makes no mathematical sense.

Important Rule

MIN/MAX on Text

MIN and MAX work alphabetically.

Example:

SELECT MIN(employee_name),
       MAX(employee_name)
FROM employees;

Could return:

Alice
Ivan

Because:

• Alice comes first alphabetically
• Ivan comes last alphabetically

5. Filtering Aggregate Results Using WHERE

This is one of the most important SQL mistakes.

Wrong:

SELECT project,
       SUM(hours_logged)
FROM employees
WHERE SUM(hours_logged) > 300
GROUP BY project;

Why This Fails

SQL execution order:

FROM
WHERE
GROUP BY
SUM()
HAVING
SELECT

WHERE runs BEFORE aggregation.

At the WHERE stage:

SUM(hours_logged)

does not exist yet.

Correct Query

SELECT project,
       SUM(hours_logged) AS total_hours
FROM employees
GROUP BY project
HAVING SUM(hours_logged) > 300;

Key Rule

6. Selecting Extra Columns with Aggregates

Wrong:

SELECT employee_name,
       AVG(years_experience)
FROM employees;

Why This Fails

AVG returns one value for the whole table.

But employee_name has many rows.

SQL becomes confused:

Which employee_name should appear beside the average?

Correct Query

SELECT project,
       AVG(years_experience)
FROM employees
GROUP BY project;

7. NULLs and COUNT(DISTINCT)

Important behavior:

COUNT(DISTINCT column)

• removes duplicates
• ignores NULL

Example

Values:

Aisha
Aisha
Rohan
NULL

Result:

2

Because:

• duplicate Aisha removed
• NULL ignored

8. GROUP BY Unique IDs with COUNT()

Wrong:

SELECT reg_id,
       COUNT(*)
FROM registrations
GROUP BY reg_id;

Why This is Wrong

reg_id is unique.

Every group contains exactly one row.

Result:

1 → 1
2 → 1
3 → 1
...

Aggregation becomes meaningless.

Better Example

SELECT event,
       COUNT(*)
FROM registrations
GROUP BY event;

Now multiple rows exist per group.

Real aggregation happens.

9. COUNT(DISTINCT) Performance Cost

COUNT(DISTINCT) is expensive on very large datasets.

Why?

Because SQL must:

1. compare values
2. remove duplicates
3. sort/hash internally

This becomes slow on millions of rows.

Important Rule

Use:

COUNT(*)

whenever uniqueness is NOT required.

Use DISTINCT only when necessary.

10. Spaces in Text Cause Wrong DISTINCT Counts

SQL treats:

'Aisha'

and

'Aisha '

as different values.

Trailing spaces can produce incorrect DISTINCT counts.

Example

COUNT(DISTINCT user_name)

might count:

Aisha
Aisha(space)

as two separate users.

Best Practice

Clean data using:

TRIM()

before aggregation.

11. Using HAVING Instead of WHERE

Wrong approach:

SELECT city,
       COUNT(*)
FROM users
GROUP BY city
HAVING city = 'Delhi';

Why This is Inefficient

SQL:

1. groups ALL cities first
2. then removes unwanted cities

Very slow on huge tables.

Better Query

SELECT city,
       COUNT(*)
FROM users
WHERE city = 'Delhi'
GROUP BY city;

Key Rule

Use:

• WHERE for simple row filtering
• HAVING for aggregate filtering

12. Alias Issues Inside HAVING

Some databases allow:

HAVING registrations > 3

Some databases reject it.

Safer Approach

Always write:

HAVING COUNT(*) > 3

instead of relying on aliases.

13. Using HAVING Without GROUP BY

HAVING is mainly designed for grouped results.

Wrong usage:

SELECT *
FROM employees
HAVING salary > 50000;

Why This is Wrong

HAVING is meant for aggregate filtering.

Simple conditions should use WHERE.

Correct Query

SELECT *
FROM employees
WHERE salary > 50000;

Final Mental Model

GROUP BY  → creates buckets
SUM       → totals inside buckets
AVG       → averages inside buckets
MIN/MAX   → smallest/largest inside buckets
COUNT     → counts inside buckets
HAVING    → filters buckets
WHERE     → filters rows before grouping