SQL Aggregate Functions, GROUP BY, and the HAVING Clause

What are Aggregate Functions? (In Simple Words)

In ordinary SQL queries, an operation works on one row at a time (e.g., checking if Age > 20).

An Aggregate Function takes multiple values across a column from many rows, crunches them together, and returns a single summarized numerical result (like finding the class average marks, or total company payroll).

1. The 5 Core Built-In Aggregate Functions

The Five Built-In Aggregate Functions in SQL

1. COUNT()

Returns the total number of rows or non-null values.

2. SUM()

Calculates the total mathematical sum of a numeric column.

3. AVG()

Calculates the arithmetic mean value of a numeric column.

4. MIN()

Returns the lowest (minimum) value in a column.

5. MAX()

Returns the highest (maximum) value in a column.

Practical Examples of Basic Aggregates:

Table: Employee (Emp_ID, Name, Salary, Dept_ID)

-- 1. Count total number of employees
SELECT COUNT(*) AS Total_Employees FROM Employee;

-- 2. Count employees who have a non-null email
SELECT COUNT(Email) AS Valid_Emails FROM Employee;

-- 3. Find the total monthly payroll of the company
SELECT SUM(Salary) AS Total_Payroll FROM Employee;

-- 4. Calculate the average salary
SELECT AVG(Salary) AS Average_Salary FROM Employee;

-- 5. Find the highest and lowest salary
SELECT MIN(Salary) AS Lowest_Salary, MAX(Salary) AS Highest_Salary 
FROM Employee;

📌 Important Rule: All aggregate functions (except COUNT(*)) automatically ignore NULL values during calculations!

2. Grouping Rows with the `GROUP BY` Clause

The GROUP BY statement groups rows that have the same values in specified columns into summary rows (like “find the average salary for EACH department”).

General Syntax:

SELECT Column_Name, Aggregate_Function(Column_Name)
FROM Table_Name
WHERE Condition
GROUP BY Column_Name;

Practical Example:

-- Calculate the average salary and employee count for EACH department
SELECT 
    Dept_ID,
    COUNT(*) AS Number_Of_Staff,
    AVG(Salary) AS Avg_Department_Salary
FROM Employee
GROUP BY Dept_ID;

⚠️ The Golden SQL Rule for GROUP BY:
Every column listed in the SELECT clause that is NOT enclosed inside an aggregate function MUST be included in the GROUP BY clause!

3. Filtering Groups with the `HAVING` Clause

What if you want to filter out entire groups?
(e.g., “Show only departments that have more than 5 employees” or “departments where average salary > 50,000”).

You CANNOT use WHERE with aggregate functions! Writing WHERE AVG(Salary) > 50000 causes an immediate SQL syntax error!
Why? Because the WHERE clause filters individual rows BEFORE groups are formed.
The HAVING clause was introduced specifically to filter groups AFTER aggregation has occurred!

-- Find departments where the average salary is greater than 50,000
SELECT 
    Dept_ID,
    AVG(Salary) AS Average_Salary
FROM Employee
GROUP BY Dept_ID
HAVING AVG(Salary) > 50000;

4. Crucial Comparison: `WHERE` vs. `HAVING`

This is one of the most frequently asked 5-mark questions in DAVV examinations:

Feature	`WHERE` Clause	`HAVING` Clause
Primary Purpose	Filters individual rows (tuples)	Filters summarized groups
Execution Timing	Executes BEFORE `GROUP BY`	Executes AFTER `GROUP BY`
Use with Aggregates	Cannot be used with aggregate functions	Can and is used with aggregate functions (`COUNT`, `SUM`, `AVG`)
Dependency	Can be used independently without `GROUP BY`	Almost always used alongside `GROUP BY`
Performance	Fast (reduces dataset size early before grouping)	Applied later (filters grouped result set)

5. Complete Query Anatomy (Execution Order)

When a complex SQL query contains all clauses, how does the database execute it?

SELECT Dept_ID, AVG(Salary) AS Avg_Sal
FROM Employee
WHERE Age >= 21
GROUP BY Dept_ID
HAVING COUNT(*) >= 3
ORDER BY Avg_Sal DESC;

Actual Execution Order in the Database Engine:

FROM Employee ➔ Locates table on disk.
WHERE Age >= 21 ➔ Discards employees younger than 21.
GROUP BY Dept_ID ➔ Groups remaining records by department.
HAVING COUNT(*) >= 3 ➔ Discards groups with fewer than 3 employees.
SELECT Dept_ID, AVG(Salary) ➔ Computes final summary projections.
ORDER BY Avg_Sal DESC ➔ Sorts the final output in descending order!

💡 DAVV Exam Preparation Tips

5-Mark Question: “Differentiate between WHERE and HAVING clauses in SQL with a syntax example.”

Explain that WHERE filters rows before grouping, while HAVING filters aggregated groups after grouping.

Show why WHERE AVG(Salary) > 50000 is illegal, and write the valid GROUP BY Dept_ID HAVING AVG(Salary) > 50000 query.

10-Mark Question: “Explain the five built-in aggregate functions in SQL. Describe the GROUP BY and HAVING clauses with complete query examples on an EMPLOYEE table. What is the logical execution order of SQL clauses?”

Define COUNT, SUM, AVG, MIN, MAX.

Write queries demonstrating single-column grouping, multi-column grouping, and HAVING conditions.

Walk through the 6-step internal query execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY).

SQL Aggregate Functions, GROUP BY & HAVING Clause