SQL Aggregate Functions, GROUP BY & HAVING

Aggregate Functions (also known as Multiple-Row or Group Functions) operate on a group of rows and return a single summary value for each group. When combined with the GROUP BY and HAVING clauses, they form the analytical backbone of SQL.

---

1. Aggregate Functions Overview

MySQL provides five fundamental aggregate functions prescribed in the CBSE IP syllabus:

SELECT 
    COUNT(*), 
    COUNT(Commission), 
    SUM(Salary), 
    AVG(Salary), 
    MAX(Salary), 
    MIN(Salary) 
FROM Employee;

Critical Rules for Aggregate Functions

1. NULL Value Handling: All aggregate functions except COUNT(*) completely ignore NULL values.
2. COUNT(*) vs COUNT(col):
   • COUNT(*) counts the total number of records/rows in the table or group, including NULLs and duplicates.
   • COUNT(column_name) counts only the non-NULL values in that specific column.
   • COUNT(DISTINCT column_name) counts unique, non-NULL values.
3. AVG(col) Calculation:$$\text{AVG}(col)=\frac{\text{SUM}(col)}{\text{COUNT}(col)}(\text{denominator is count of non-NULLs, NOT total rows})$$

---

2. Sample Dataset for Reference: EMPLOYEE

EmpId	Name	Dept	Salary	Commission	JoinDate
101	'Aarav'	'IT'	65000	5000	2022-01-15
102	'Bhavna'	'HR'	48000	NULL	2021-06-20
103	'Chirag'	'IT'	72000	8000	2020-03-10
104	'Divya'	'Sales'	52000	6000	2023-09-01
105	'Eshan'	'HR'	51000	NULL	2022-11-12
106	'Farah'	'Sales'	55000	7500	2021-04-18

Aggregate Evaluation on Sample Dataset:

SELECT COUNT(*) FROM Employee;
-- Result: 6 (Total tuples)

SELECT COUNT(Commission) FROM Employee;
-- Result: 4 (Rows with non-NULL Commission: 5000, 8000, 6000, 7500)

SELECT SUM(Commission) FROM Employee;
-- Result: 26500 (5000 + 8000 + 6000 + 7500)

SELECT AVG(Commission) FROM Employee;
-- Result: 6625 (26500 / 4, NOT 26500 / 6)

---

3. The GROUP BY Clause

The GROUP BY clause divides the rows of a table into distinct groups based on matching values in one or more specified columns. Aggregate functions are then applied to each group independently.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
[WHERE condition]
GROUP BY column1;

Example: Average Salary by Department

SELECT Dept, COUNT(*) AS TotalStaff, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept;

Output:

Dept	TotalStaff	AvgSalary
IT	2	68500.00
HR	2	49500.00
Sales	2	53500.00

---

4. The HAVING Clause vs WHERE Clause

IMPORTANT

Cardinal Rule of SQL: The WHERE clause filters individual records before grouping occurs and cannot contain aggregate functions. The HAVING clause filters summarized groups after GROUP BY and is used with aggregate conditions.

Detailed Comparison

Feature	WHERE Clause	HAVING Clause
Stage of Execution	Applied before grouping (GROUP BY)	Applied after grouping (GROUP BY)
Works On	Individual rows (tuples)	Summarized groups
Can use Aggregate Functions?	❌ Strictly NO (WHERE AVG(Sal) > 5000 is an Error)	✅ YES (HAVING AVG(Salary) > 50000)
Requirement	Can be used without GROUP BY	Almost always used with GROUP BY

Combining WHERE, GROUP BY, and HAVING

SELECT Dept, AVG(Salary) AS AvgSalary, COUNT(*) AS Headcount
FROM Employee
WHERE JoinDate >= '2021-01-01'        -- Filter 1: Individual row filter
GROUP BY Dept                          -- Grouping
HAVING COUNT(*) >= 2;                 -- Filter 2: Group filter on aggregate

---

5. Order of SQL Clauses in a Query

In SQL, clauses must appear in a strictly enforced syntax order:

graph LR
    A["1. SELECT"] --> B["2. FROM"]
    B --> C["3. WHERE"]
    C --> D["4. GROUP BY"]
    D --> E["5. HAVING"]
    E --> F["6. ORDER BY [ASC|DESC]"]

SELECT Dept, SUM(Salary) AS TotalDeptSal
FROM Employee
WHERE Salary > 40000
GROUP BY Dept
HAVING SUM(Salary) > 100000
ORDER BY TotalDeptSal DESC;

---

6. Common Board Traps & Error Analysis

Trap 1: Aggregate Function in WHERE Clause

-- ❌ SYNTAX ERROR:
SELECT Dept, AVG(Salary) FROM Employee WHERE AVG(Salary) > 50000 GROUP BY Dept;

-- ✅ CORRECT:
SELECT Dept, AVG(Salary) FROM Employee GROUP BY Dept HAVING AVG(Salary) > 50000;

Trap 2: Non-Aggregated Column in SELECT with GROUP BY

In standard SQL, any column present in the SELECT list that is not part of an aggregate function must be included in the GROUP BY clause.

-- ❌ LOGICAL INVALIDITY:
SELECT Name, Dept, MAX(Salary) FROM Employee GROUP BY Dept;
-- (Which Name should SQL choose for each department?)

-- ✅ CORRECT:
SELECT Dept, MAX(Salary) FROM Employee GROUP BY Dept;

---

7. Board Practice Problems

Problem 1: Query Formulation (4 Marks)

Given a table STUDENT(AdmNo, Name, Stream, Marks, ActivityFee):

1. Display the Stream and highest Marks scored in each Stream.
2. Display the total number of students in each Stream where the average Marks is greater than 75.
3. Display the total ActivityFee collected for students having Marks $>80$, grouped by Stream.
4. Count the number of students who have paid their ActivityFee (ignoring NULLs).

SQL Solutions:

-- 1.
SELECT Stream, MAX(Marks) 
FROM Student 
GROUP BY Stream;

-- 2.
SELECT Stream, COUNT(*) 
FROM Student 
GROUP BY Stream 
HAVING AVG(Marks) > 75;

-- 3.
SELECT Stream, SUM(ActivityFee) 
FROM Student 
WHERE Marks > 80 
GROUP BY Stream;

-- 4.
SELECT COUNT(ActivityFee) 
FROM Student;