Aggregation and Data Summarisation

GROUP BY and HAVING are clauses, not aggregate functions. COUNT(*) counts rows; aggregate functions such as SUM(column) and AVG(column) ignore NULL inputs.

Turning Raw Data into Meaningful Insights

After learning how to:

• retrieve rows using SELECT
• filter rows using WHERE
• sort results using ORDER BY

…the next major skill in SQL is:

Data Summarization

In real-world systems, companies rarely analyze raw rows one by one.

Instead, businesses ask questions like:

• What is the total revenue this month?
• Which project consumed the most effort?
• What is the average employee experience?
• Which event has the highest registrations?
• Which department has the most employees?

To answer these types of questions, SQL provides:

• GROUP BY
• SUM()
• AVG()
• MIN()
• MAX()
• COUNT()
• HAVING

These are called:

Aggregate Functions

They help us convert raw row-level data into meaningful business insights.

Dataset Used

We will use this employees table throughout the lesson.

CREATE TABLE employees (
  emp_id           INT PRIMARY KEY,
  employee_name    VARCHAR(50) NOT NULL,
  project          VARCHAR(20) NOT NULL,
  years_experience DECIMAL(4,1) NULL,
  hours_logged     INT NULL,
  role             VARCHAR(20) NULL,
  INDEX idx_project (project)
);

INSERT INTO employees (emp_id, employee_name, project, years_experience, hours_logged, role) VALUES
(1,  'Alice', 'Alpha', 3.0, 100, 'Developer'),
(2,  'Bob',   'Alpha', 5.0, 120, 'QA'),
(3,  'Carol', 'Alpha', 7.0, 105, 'Developer'),
(4,  'Dave',  'Alpha', NULL, 0, 'Manager'),
(5,  'Eve',   'Beta',  2.0,  80, 'Manager'),
(6,  'Frank', 'Beta',  3.0, 110, 'Developer'),
(7,  'Grace', 'Beta',  4.0, 130, 'Developer'),
(8,  'Hank',  'Beta',  3.0, NULL, 'Developer'),
(9,  'Heidi', 'Gamma', 5.0, 150, 'Developer'),
(10, 'Ivan',  'Gamma', 6.0, 140, 'QA');

Employee Table

1. GROUP BY

What is GROUP BY?

GROUP BY divides rows into groups based on one or more columns.

After grouping:

• aggregate functions operate inside each group separately

Mental Model

Think of GROUP BY like sorting employees into separate rooms.

Example:

GROUP BY project

creates:

• Alpha room
• Beta room
• Gamma room

Now SQL performs calculations separately inside each room.

Example 1 — List All Unique Projects

SELECT project
FROM employees
GROUP BY project;

Output

project

Alpha

Beta

Gamma

What Happened Internally?

Without GROUP BY:

Alpha
Alpha
Alpha
Alpha
Beta
Beta
Beta
Beta
Gamma
Gamma

After grouping:

Alpha
Beta
Gamma

Duplicate values collapse into one group.

Use Case

Useful for:

• listing departments
• listing categories
• listing unique teams
• creating report buckets

Example 2 — Group by Multiple Columns

SELECT project, role
FROM employees
GROUP BY project, role;

Output

What Happened?

Now SQL creates groups using BOTH columns.

Example:

(Alpha, Developer)
(Alpha, Manager)
(Alpha, QA)

Each unique combination becomes its own group.

Logical Execution Order in SQL

This is extremely important.

SQL is written top-to-bottom.

But internally SQL executes differently.

Actual SQL Execution Order

1. FROM
2. WHERE
3. GROUP BY
4. Aggregate Functions
5. HAVING
6. SELECT
7. ORDER BY

Why This Matters

This explains many SQL rules.

Example:

You cannot use aggregate functions inside WHERE because:

WHERE executes BEFORE aggregation

That is why SQL provides:

HAVING

for filtering aggregated results.

2. MIN() and MAX()

What is MIN()?

MIN() returns the smallest value in a column.

What is MAX()?

MAX() returns the largest value in a column.

Real-Life Usage

Used for:

• lowest salary
• highest marks
• earliest date
• latest transaction
• junior-most employee
• senior-most employee

Example — Experience Range Per Project

SELECT project,
       MIN(years_experience) AS min_experience,
       MAX(years_experience) AS max_experience
FROM employees
GROUP BY project;

Output

Intuition

For every project:

Find:

• least experienced employee
• most experienced employee

What SQL Does Internally

Alpha Team

3, 5, 7, NULL

• MIN = 3
• MAX = 7

NULL ignored automatically.

Important Rule

Aggregate functions ignore NULL values.

This is extremely important.

3. SUM() and AVG()

What is SUM()?

SUM() adds numeric values together.

What is AVG()?

AVG() calculates the average.

Formula:

SUM(values) / COUNT(non-null values)

Example — Project Workload Analysis

SELECT project,
       SUM(hours_logged) AS total_hours,
       AVG(years_experience) AS avg_experience
FROM employees
GROUP BY project;

Output

What Happened?

Alpha Team

Hours:

100 + 120 + 105 + 0 = 325

Experience:

(3 + 5 + 7) / 3 = 5

Dave’s NULL experience ignored.

Real Business Use Cases

Used for:

• total revenue
• average ratings
• team workload
• average salary
• total expenses
• employee productivity

Important Rule About AVG()

AVG ignores NULL values.

This is very important.

Example:

3, 5, 7, NULL

AVG becomes:

(3 + 5 + 7) / 3

NOT divided by 4.

4. HAVING

What is HAVING?

HAVING filters groups AFTER aggregation.

Difference Between WHERE and HAVING

Mental Model

Think of:

WHERE

as filtering employees BEFORE creating teams.

And:

HAVING

as filtering teams AFTER calculations are done.

Example — Projects with High Workload

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

Output

What Happened Internally?

Step 1:

Groups created:

• Alpha
• Beta
• Gamma

Step 2:

Totals calculated:

Step 3:

HAVING filters:

> 300

Gamma removed.

5. COUNT() in SQL

What is COUNT()?

COUNT() is used to count rows or values in SQL.

It helps answer questions like:

• How many users signed up?
• How many orders were placed?
• How many products were sold?
• How many unique customers exist?

Important Concept

COUNT behaves differently depending on what you count.

There are 3 major forms:

Dataset Used

We will use this registrations table.

CREATE TABLE registrations (
  reg_id      INT PRIMARY KEY,
  user_name   VARCHAR(50) NULL,
  email       VARCHAR(120) NULL,
  event       VARCHAR(50) NOT NULL,
  ticket_type ENUM('Free','Paid') NOT NULL,
  referrer    VARCHAR(50) NULL,
  INDEX idx_event (event)
);

INSERT INTO registrations (reg_id, user_name, email, event, ticket_type, referrer) VALUES
(1,  'Aisha',  'aisha@example.com',   'TechFest',  'Paid', 'Instagram'),
(2,  'Rohan',  'rohan@example.com',   'TechFest',  'Free', NULL),
(3,  'Aisha',  'aisha@example.com',   'CodeCamp',  'Paid', 'Instagram'),
(4,  'Mohit',  NULL,                  'TechFest',  'Free', 'LinkedIn'),
(5,  'Neha',   'neha@example.com',    'CodeCamp',  'Free', 'Instagram'),
(6,  NULL,     'unknown@example.com', 'DesignCon', 'Paid', 'Twitter'),
(7,  'Aisha',  'aisha@example.com',   'TechFest',  'Paid', 'Instagram'),
(8,  'Vishal', NULL,                  'DesignCon', 'Free', NULL),
(9,  'Rohan',  'rohan@example.com',   'TechFest',  'Free', 'Instagram'),
(10, 'Aisha',  'aisha@example.com',   'CodeCamp',  'Paid', NULL);

Registrations Table

Scenario 1 — COUNT(*)

What Does COUNT(*) Do?

COUNT(*) counts every row in the table.

It does NOT care about:

• NULL values
• duplicates
• missing data

If the row exists, it is counted.

Query

SELECT
    COUNT(*) AS total_registrations
FROM registrations;

Output

total_registrations

10

Intuition

Think of COUNT(*) as:

“How many total records physically exist inside this table?”

Even rows with:

• NULL email
• NULL user_name
• NULL referrer

are still counted.

Internal Understanding

SQL simply counts rows:

Row 1 → counted
Row 2 → counted
Row 3 → counted
...
Row 10 → counted

Final count = 10

Real-Life Use Cases

Used for:

• total users
• total orders
• total transactions
• total visits
• total registrations

Scenario 2 — COUNT(column)

What Does COUNT(column) Do?

COUNT(column) counts ONLY rows where that column is NOT NULL.

NULL values are skipped automatically.

Query

SELECT
    COUNT(email) AS emails_provided
FROM registrations;

Output

emails_provided

8

Why Output is 8?

Because 2 rows contain NULL emails:

SQL ignores those rows.

Internal Understanding

SQL processes like this:

aisha@example.com → counted
rohan@example.com → counted
NULL → ignored
...

Final count = 8

Important Rule

COUNT(column)

ALWAYS ignores NULL values.

This is one of the most important SQL interview concepts.

Real-Life Use Cases

Used for:

• how many users filled phone number
• how many orders have delivery address
• how many students submitted assignments
• how many customers added profile pictures

Scenario 3 — COUNT(DISTINCT column)

What Does COUNT(DISTINCT) Do?

COUNT(DISTINCT column):

1. removes duplicates
2. ignores NULLs
3. counts unique values only

Query

SELECT
    COUNT(DISTINCT user_name) AS unique_users
FROM registrations;

Output

unique_users

5

What Happened Internally?

Raw user names:

Aisha
Rohan
Aisha
Mohit
Neha
NULL
Aisha
Vishal
Rohan
Aisha

After DISTINCT:

Aisha
Rohan
Mohit
Neha
Vishal

NULL ignored.

Final count = 5

Important Rule

COUNT(DISTINCT):

• removes duplicates
• ignores NULL

Real-Life Use Cases

Used for:

• unique customers
• unique visitors
• unique products sold
• unique login users
• unique email addresses

Scenario 4 — COUNT() with GROUP BY

Goal

Count registrations separately for each event.

Query

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

Output

What Happened Internally?

Step 1 — GROUP BY creates groups

TechFest group
CodeCamp group
DesignCon group

Step 2 — COUNT(*) runs inside each group

Internal Visualization

TechFest

5 rows

CodeCamp

3 rows

DesignCon

2 rows

Mental Model

Think of GROUP BY as creating separate buckets.

COUNT runs independently inside each bucket.

Real-Life Use Cases

Used for:

• orders per city
• users per country
• sales per category
• registrations per event
• employees per department

1. COUNT(DISTINCT) with GROUP BY

This combines COUNT(DISTINCT ...) with grouping.

Example:

SELECT event,
       COUNT(DISTINCT user_name) AS unique_users
FROM registrations
GROUP BY event;

Concept:

• GROUP BY creates event buckets
• DISTINCT removes duplicate users inside each event
• COUNT counts unique users per event

Very important for:

• DAU/MAU analytics
• unique buyers
• unique visitors

2. HAVING with COUNT()

Example:

SELECT event,
       COUNT(*) AS registrations
FROM registrations
GROUP BY event
HAVING COUNT(*) > 3;

Concept:

• GROUP BY first
• COUNT calculated
• HAVING filters aggregated groups

This is one of the most important SQL interview concepts.

3. COUNT() vs COUNT(column) vs COUNT(DISTINCT)

A final comparison:

This comparison is extremely important for interviews.

4. Important COUNT Rules (Very Important)

The main rules to remember:

Rule 1

COUNT(*) counts everything.

Rule 2

COUNT(column) ignores NULL.

Rule 3

COUNT(DISTINCT column):

• ignores duplicates
• ignores NULL

Rule 4

Aggregate functions ignore NULL values.

Applies to:

• AVG
• SUM
• MIN
• MAX
• COUNT(column)

5. Common Mistakes Section

Very important to add.

Examples:

Mistake 1 — Using WHERE with aggregate

Wrong:

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

Correct:

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

Because:

• WHERE executes before aggregation
• HAVING executes after aggregation

Mistake 2 — Forgetting GROUP BY

Wrong:

SELECT project, AVG(hours_logged)
FROM employees;

Error because:

• project is non-aggregated
• AVG is aggregated

Correct:

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

Mistake 3 — Confusing COUNT(*) and COUNT(column)

COUNT(*)          → counts rows
COUNT(email)      → ignores NULL emails

6. Final Mental Model Summary

This is highly recommended at the end of Day 22.

GROUP BY  → create buckets
SUM       → total inside bucket
AVG       → average inside bucket
MIN/MAX   → smallest/largest inside bucket
COUNT     → how many inside bucket
HAVING    → filter buckets

This creates long-term memory retention.