What is Aggregation?

Aggregation means:

Taking multiple rows and producing a single summarized value.

Instead of looking at every employee individually, you ask questions like:

• How many employees are there?
• What's the average salary?
• What's the highest salary?
• What's the total salary paid?
• How many employees are in each department?

These are aggregate questions.

Example Table

Employees

Aggregate Functions

There are five main aggregate functions.

1. COUNT()

Counts rows.

Count all employees:

SELECT COUNT(*)
FROM Employees;

Count only IT employees:

SELECT COUNT(*)
FROM Employees
WHERE Department = 'IT';

COUNT(column)

SELECT COUNT(Salary)
FROM Employees;

This counts non-NULL salary values.

Important Interview Question

Difference between:

COUNT(*)

and:

COUNT(column)

Answer:

• COUNT(*) counts all rows.
• COUNT(column) counts only rows where that column is NOT NULL.

Example:

COUNT(*) returns 3.

COUNT(Bonus) returns 2.

2. SUM()

Adds values.

SELECT SUM(Salary)
FROM Employees;

Output:

360000

3. AVG()

Returns average.

SELECT AVG(Salary)
FROM Employees;

4. MIN()

Returns the smallest value.

SELECT MIN(Salary)
FROM Employees;

5. MAX()

Returns the largest value.

SELECT MAX(Salary)
FROM Employees;

GROUP BY

This is the most important part of aggregation.

Without GROUP BY:

SELECT AVG(Salary)
FROM Employees;

This returns the average of everyone.

Suppose the manager asks:

Show average salary for each department.

Now one average isn't enough. We need one average per department.

That is exactly what GROUP BY does.

GROUP BY Mental Model

It groups rows that have the same value.

Employees
    |
    v
Separate into groups
    |
    v
Perform aggregation

Average salary by department:

SELECT Department,
       AVG(Salary)
FROM Employees
GROUP BY Department;

Output:

Count employees in each department:

SELECT Department,
       COUNT(*)
FROM Employees
GROUP BY Department;

Total salary by department:

SELECT Department,
       SUM(Salary)
FROM Employees
GROUP BY Department;

Highest salary in each department:

SELECT Department,
       MAX(Salary)
FROM Employees
GROUP BY Department;

HAVING

People often confuse WHERE and HAVING.

Suppose you ask:

Show only departments having more than 2 employees.

This is wrong:

SELECT Department,
       COUNT(*)
FROM Employees
WHERE COUNT(*) > 2
GROUP BY Department;

Why?

Because WHERE executes before grouping. At that point, COUNT(*) does not exist yet.

Correct:

SELECT Department,
       COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2;

Easy Memory Trick

WHERE
  |
  v
Filters rows

GROUP BY
  |
  v
Creates groups

HAVING
  |
  v
Filters groups

SQL Logical Execution Order

For:

SELECT Department,
       COUNT(*)
FROM Employees
WHERE Salary > 50000
GROUP BY Department
HAVING COUNT(*) >= 2
ORDER BY COUNT(*) DESC;

Logical order:

FROM
  |
  v
WHERE
  |
  v
GROUP BY
  |
  v
HAVING
  |
  v
SELECT
  |
  v
ORDER BY

This explains why WHERE cannot use aggregate functions, while HAVING can.

WHERE vs HAVING

Common Interview Questions

Highest salary department:

SELECT Department,
       MAX(Salary)
FROM Employees
GROUP BY Department;

Number of employees in each department:

SELECT Department,
       COUNT(*)
FROM Employees
GROUP BY Department;

Departments with more than two employees:

SELECT Department,
       COUNT(*)
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2;

Average salary of IT department:

SELECT AVG(Salary)
FROM Employees
WHERE Department = 'IT';

We filter rows first, then compute the average. No GROUP BY is needed because we are asking about only one department.

Summary Table

Interview Questions

1. Difference between COUNT(*) and COUNT(column)?

• COUNT(*) counts all rows.
• COUNT(column) counts only non-NULL values in that column.

2. Difference between WHERE and HAVING?

• WHERE filters individual rows before grouping.
• HAVING filters groups after aggregation.

3. Why can't we use COUNT() in the WHERE clause?

Because WHERE is evaluated before GROUP BY and before aggregate values are computed. Aggregate functions are only available after grouping, which is why they belong in the HAVING clause.

Mental Model

Raw Table
    |
    v
WHERE        -> Remove unwanted rows
    |
    v
GROUP BY     -> Create groups
    |
    v
Aggregate    -> COUNT, SUM, AVG, MIN, MAX
    |
    v
HAVING       -> Remove unwanted groups
    |
    v
SELECT       -> Return final columns
    |
    v
ORDER BY     -> Sort results

If you remember this flow, you'll be able to solve most aggregation questions in interviews without getting confused.