SQL Aggregate Functions, GROUP BY, HAVING, CBSE Class 12 with Examples

Aggregate functions and GROUP BY are among the most important SQL topics for the CBSE Class 12 board exam. They appear almost every year, typically carrying 3-5 marks. This guide covers everything you need with clear examples and output.

We will use this table for all examples:

Table: EMPLOYEE

EmpID	Name	Department	Salary	City	Age
1	Amit	Sales	35000	Delhi	28
2	Priya	IT	52000	Mumbai	32
3	Rahul	Sales	40000	Delhi	25
4	Sneha	HR	45000	Kolkata	30
5	Vikram	IT	60000	Chennai	35
6	Kavita	HR	42000	Mumbai	27
7	Arjun	Sales	38000	Chennai	29
8	Meera	IT	55000	Delhi	31
9	Ravi	HR	47000	Kolkata	33
10	Deepa	Sales	36000	Mumbai	26

---

What are Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. They ignore NULL values (except COUNT(*)).

The five aggregate functions in the CBSE syllabus are:

Function	Purpose
COUNT()	Counts the number of rows
SUM()	Adds up all values
AVG()	Calculates the average
MIN()	Finds the smallest value
MAX()	Finds the largest value

---

COUNT()

COUNT() returns the number of rows that match the condition.

Count all rows:

SELECT COUNT(*) FROM EMPLOYEE;

COUNT(*)
--------
10

Count rows in a specific department:

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

COUNT(*)
--------
3

Count distinct cities:

SELECT COUNT(DISTINCT City) FROM EMPLOYEE;

COUNT(DISTINCT City)
--------------------
4

Important: COUNT(*) counts all rows including those with NULL values. COUNT(column_name) counts only non-NULL values in that column.

---

SUM()

SUM() returns the total of all values in a numeric column.

Total salary of all employees:

SELECT SUM(Salary) FROM EMPLOYEE;

SUM(Salary)
-----------
450000

Total salary of IT department:

SELECT SUM(Salary) FROM EMPLOYEE WHERE Department = 'IT';

SUM(Salary)
-----------
167000

---

AVG()

AVG() returns the average (mean) value.

Average salary of all employees:

SELECT AVG(Salary) FROM EMPLOYEE;

AVG(Salary)
-----------
45000

Average age of employees in Mumbai:

SELECT AVG(Age) FROM EMPLOYEE WHERE City = 'Mumbai';

AVG(Age)
--------
28.33

---

MIN() and MAX()

MIN() returns the smallest value; MAX() returns the largest.

Lowest and highest salary:

SELECT MIN(Salary), MAX(Salary) FROM EMPLOYEE;

MIN(Salary) | MAX(Salary)
------------|------------
35000       | 60000

Youngest and oldest employee age:

SELECT MIN(Age) AS Youngest, MAX(Age) AS Oldest FROM EMPLOYEE;

Youngest | Oldest
---------|-------
25       | 35

Note: AS is used to give an alias (temporary name) to the result column. This makes output clearer and is good practice in exams.

---

GROUP BY

GROUP BY divides rows into groups based on a column's values. Aggregate functions are then applied to each group separately.

Syntax:

SELECT column, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY column;

Example 1: Count employees in each department

SELECT Department, COUNT(*) AS Total
FROM EMPLOYEE
GROUP BY Department;

Department | Total
-----------|------
Sales      | 4
IT         | 3
HR         | 3

Example 2: Total salary by department

SELECT Department, SUM(Salary) AS TotalSalary
FROM EMPLOYEE
GROUP BY Department;

Department | TotalSalary
-----------|------------
Sales      | 149000
IT         | 167000
HR         | 134000

Example 3: Average salary by city

SELECT City, AVG(Salary) AS AvgSalary
FROM EMPLOYEE
GROUP BY City;

City     | AvgSalary
---------|----------
Delhi    | 42333.33
Mumbai   | 43333.33
Kolkata  | 46000
Chennai  | 49000

Example 4: Maximum salary in each department

SELECT Department, MAX(Salary) AS HighestSalary
FROM EMPLOYEE
GROUP BY Department;

Department | HighestSalary
-----------|-------------
Sales      | 40000
IT         | 60000
HR         | 47000

Example 5: Group by multiple columns

SELECT Department, City, COUNT(*) AS Total
FROM EMPLOYEE
GROUP BY Department, City;

Department | City     | Total
-----------|----------|------
Sales      | Delhi    | 2
Sales      | Chennai  | 1
Sales      | Mumbai   | 1
IT         | Mumbai   | 1
IT         | Chennai  | 1
IT         | Delhi    | 1
HR         | Kolkata  | 2
HR         | Mumbai   | 1

---

HAVING Clause

HAVING filters groups after GROUP BY has been applied. It works like WHERE, but for groups.

Syntax:

SELECT column, AGGREGATE_FUNCTION(column)
FROM table
GROUP BY column
HAVING condition;

Example 1: Departments with more than 3 employees

SELECT Department, COUNT(*) AS Total
FROM EMPLOYEE
GROUP BY Department
HAVING COUNT(*) > 3;

Department | Total
-----------|------
Sales      | 4

Only Sales has more than 3 employees, so only it appears.

Example 2: Departments where average salary exceeds 45000

SELECT Department, AVG(Salary) AS AvgSalary
FROM EMPLOYEE
GROUP BY Department
HAVING AVG(Salary) > 45000;

Department | AvgSalary
-----------|----------
IT         | 55666.67

Example 3: Cities with total salary above 80000

SELECT City, SUM(Salary) AS TotalSalary
FROM EMPLOYEE
GROUP BY City
HAVING SUM(Salary) > 80000;

City     | TotalSalary
---------|------------
Delhi    | 127000
Mumbai   | 130000
Kolkata  | 92000
Chennai  | 98000

---

WHERE vs HAVING, Key Difference

This is a very common exam question. Understand the difference clearly.

Feature	WHERE	HAVING
Filters	Individual rows	Groups (after GROUP BY)
Used with	Any SELECT query	Only with GROUP BY
Aggregate functions	Cannot use aggregate functions	Can use aggregate functions
Execution order	Before grouping	After grouping

Example showing both WHERE and HAVING together:

SELECT Department, AVG(Salary) AS AvgSalary
FROM EMPLOYEE
WHERE Age > 25
GROUP BY Department
HAVING AVG(Salary) > 45000;

Department | AvgSalary
-----------|----------
IT         | 55666.67
HR         | 44666.67

How this works step by step:

1. WHERE Age > 25 first filters out employees aged 25 or below (Rahul is removed)
2. GROUP BY Department groups the remaining rows by department
3. HAVING AVG(Salary) > 45000 keeps only groups where the average salary exceeds 45000

---

Order of Clauses in SQL

The correct order of clauses in a SELECT statement is:

SELECT column(s)
FROM table
WHERE condition          -- filter rows
GROUP BY column          -- group rows
HAVING condition         -- filter groups
ORDER BY column;         -- sort result

Complete example using all clauses:

SELECT Department, COUNT(*) AS Total, AVG(Salary) AS AvgSalary
FROM EMPLOYEE
WHERE City != 'Kolkata'
GROUP BY Department
HAVING COUNT(*) >= 2
ORDER BY AvgSalary DESC;

Department | Total | AvgSalary
-----------|-------|----------
IT         | 2     | 56000
Sales      | 3     | 37666.67

---

Board Exam Questions with Answers

Q1: Write a query to display the number of employees in each city. (2 marks)

SELECT City, COUNT(*) AS NumEmployees
FROM EMPLOYEE
GROUP BY City;

Q2: Write a query to display departments where the total salary is more than 140000. (3 marks)

SELECT Department, SUM(Salary) AS TotalSalary
FROM EMPLOYEE
GROUP BY Department
HAVING SUM(Salary) > 140000;

Department | TotalSalary
-----------|------------
Sales      | 149000
IT         | 167000

Q3: What is the difference between WHERE and HAVING? Give an example. (3 marks)

Answer: WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. WHERE cannot use aggregate functions, but HAVING can.

Example: To find departments where the average salary of employees older than 28 exceeds 50000:

SELECT Department, AVG(Salary)
FROM EMPLOYEE
WHERE Age > 28
GROUP BY Department
HAVING AVG(Salary) > 50000;

Here, WHERE removes rows where age is 28 or less, and HAVING removes groups where the average salary is not above 50000.

Q4: Write the output of the following query: (2 marks)

SELECT Department, MIN(Salary), MAX(Salary)
FROM EMPLOYEE
GROUP BY Department;

Answer:

Department | MIN(Salary) | MAX(Salary)
-----------|-------------|------------
Sales      | 35000       | 40000
IT         | 52000       | 60000
HR         | 42000       | 47000

Q5: Can we use HAVING without GROUP BY? (1 mark)

Answer: Technically, HAVING can be used without GROUP BY (it treats the entire table as one group), but in practice and in CBSE exams, HAVING is always used with GROUP BY.

---

Common Mistakes to Avoid

1. Using WHERE with aggregate functions, WHERE SUM(Salary) > 50000 is wrong. Use HAVING SUM(Salary) > 50000 instead.
2. Forgetting GROUP BY when using aggregates with non-aggregated columns, SELECT Department, COUNT(*) FROM EMPLOYEE without GROUP BY will cause an error in most databases.
3. Wrong clause order, Always follow SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
4. Confusing COUNT(*) and COUNT(column), COUNT(*) counts all rows; COUNT(column) skips NULL values.

Master these aggregate functions and clauses, and the SQL section of your CBSE board exam will be straightforward.