SQL: Aggregate Functions and GROUP BY

Aggregate functions perform calculations across multiple rows and return a single result. Combined with GROUP BY, they allow you to summarise data — for example, calculating averages, counting records, or finding the highest value within groups.

---

Aggregate Functions

Function	Description	Example
COUNT()	Counts the number of rows	COUNT(*), COUNT(column)
SUM()	Adds up all values in a column	SUM(Price)
AVG()	Calculates the arithmetic mean	AVG(Score)
MIN()	Returns the smallest value	MIN(DateOfBirth)
MAX()	Returns the largest value	MAX(Score)

Examples

-- How many students are there?
SELECT COUNT(*) AS TotalStudents
FROM Students;

-- What is the average grade score?
SELECT AVG(Score) AS AverageScore
FROM Grades;

-- What is the highest score?
SELECT MAX(Score) AS HighestScore
FROM Grades;

-- What is the total cost of all products?
SELECT SUM(Price) AS TotalValue
FROM Products;

COUNT(*) vs COUNT(column)

• COUNT(*) counts all rows, including those with NULL values.
• COUNT(column) counts only rows where that column is not NULL.

-- Count all students
SELECT COUNT(*) FROM Students;

-- Count students who have an email address
SELECT COUNT(Email) FROM Students;

---

GROUP BY

GROUP BY groups rows that share the same value in a specified column, so aggregate functions can be applied to each group separately.

Basic Syntax

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

Examples

-- Average score per subject
SELECT Subject, AVG(Score) AS AvgScore
FROM Grades
GROUP BY Subject;

Result:

Subject	AvgScore
Maths	88.0
English	72.0
Science	68.0

-- Number of students in each form group
SELECT FormGroup, COUNT(*) AS NumberOfStudents
FROM Students
GROUP BY FormGroup;

-- Total sales per product category
SELECT Category, SUM(Price * Quantity) AS TotalSales
FROM OrderLines
JOIN Products ON OrderLines.ProductID = Products.ProductID
GROUP BY Category;

Grouping by Multiple Columns

-- Average score per subject per form group
SELECT FormGroup, Subject, AVG(Score) AS AvgScore
FROM Students s
JOIN Grades g ON s.StudentID = g.StudentID
GROUP BY FormGroup, Subject
ORDER BY FormGroup, Subject;

---

HAVING — Filtering Groups

WHERE filters individual rows before grouping. HAVING filters groups after grouping.

Syntax