Unit 1: Database Concepts (RDBMS)
Chapter 6: Aggregate Functions and GROUP BY / HAVING
1. What are Aggregate (Group) Functions?
Aggregate functions perform a calculation on a set of values in a column and return a single summary value.
All aggregate functions (except COUNT(*)) automatically ignore NULL values.
The Five Standard SQL Aggregate Functions
COUNT(): Returns the number of values or rows.SUM(): Returns the arithmetic sum of numeric values.AVG(): Returns the arithmetic average of numeric values.MIN(): Returns the minimum value (works on numbers, text, dates).MAX(): Returns the maximum value (works on numbers, text, dates).
2. Detailed Behavior of Aggregate Functions
COUNT(*) vs. COUNT(column_name)
COUNT(*)counts all rows in the table meeting condition, including rows with NULLs.COUNT(column)counts only rows where the specified column is NOT NULL.
-- Counts total student records
SELECT COUNT(*) FROM Student;
-- Counts how many students have provided an email (ignores NULLs)
SELECT COUNT(Email) FROM Student;
-- Counts unique streams
SELECT COUNT(DISTINCT Stream) FROM Student;
SUM() and AVG()
SELECT SUM(Marks) AS Total_Marks, AVG(Marks) AS Average_Marks
FROM Student
WHERE Stream = 'Science';
MIN() and MAX()
SELECT MIN(Marks) AS Lowest_Marks, MAX(Marks) AS Top_Score
FROM Student;
3. Grouping Rows: The GROUP BY Clause
The GROUP BY clause groups rows sharing identical values in specified columns into summary rows.
Fundamental Rule of GROUP BY
Important
Any column present in the SELECT list that is not inside an aggregate function MUST be included in the GROUP BY clause.
-- Find total students and average marks in each stream
SELECT Stream, COUNT(*) AS Total_Students, AVG(Marks) AS Avg_Score
FROM Student
GROUP BY Stream;
4. Filtering Groups: The HAVING Clause
The HAVING clause filters summarized groups produced by GROUP BY.
Crucial Distinction: WHERE vs. HAVING
| Feature | WHERE Clause | HAVING Clause |
|---|---|---|
| Applied To | Individual rows before grouping | Summarized groups after GROUP BY |
| Can Use Aggregates? | NO (e.g., WHERE AVG(Marks) > 80 is illegal) |
YES (e.g., HAVING AVG(Marks) > 80) |
| Execution Phase | Filters source table records first | Evaluates aggregate calculations |
Example Combining WHERE, GROUP BY, and HAVING
-- Find streams where average marks of female students exceeds 85
SELECT Stream, AVG(Marks) AS Avg_Marks, COUNT(*) AS Total
FROM Student
WHERE Gender = 'F'
GROUP BY Stream
HAVING AVG(Marks) > 85;
5. Board Exam Output Questions
Given Table TEACHER:
| TID | TName | Dept | Salary | Experience |
| :--- | :--- | :--- | :--- | :--- |
| 1 | Sunita | IT | 55000 | 8 |
| 2 | Manoj | Science | 62000 | 12 |
| 3 | Priya | IT | 58000 | 9 |
| 4 | Alok | Science | 45000 | 4 |
| 5 | Geeta | Commerce| 52000 | 7 |
Predict output of:
1. SELECT Dept, COUNT(*), MAX(Salary) FROM TEACHER GROUP BY Dept;
- Output:
Dept | COUNT(*) | MAX(Salary)
IT | 2 | 58000
Science | 2 | 62000
Commerce | 1 | 52000
2. SELECT Dept, AVG(Salary) FROM TEACHER GROUP BY Dept HAVING COUNT(*) > 1;
- Output:
Dept | AVG(Salary)
IT | 56500.00
Science | 53500.00