SQL Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single summary value. They are used to answer questions like "How many students are there?", "What is the total fees collected?", or "What is the highest fee paid?"

Think of aggregate functions like calculators that work on an entire column — or on groups of rows — and return one number as the answer.

The Reference Table

Note: Karan Joshi's Age is NULL to demonstrate how aggregate functions handle missing values.

The 5 Core Aggregate Functions

1. COUNT()

The COUNT() function counts rows. There are two important variations:

COUNT(*) — Counts All Rows (Including NULLs)

SELECT COUNT(*) AS TotalStudents FROM Students;

Result: 7 (counts all rows, regardless of NULL values)

COUNT(column) — Counts Non-NULL Values Only

SELECT COUNT(Age) AS StudentsWithAge FROM Students;

Result: 6 (Karan's Age is NULL, so he is not counted)

COUNT(DISTINCT column) — Counts Unique Values

SELECT COUNT(DISTINCT City) AS UniqueCities FROM Students;

Result: 4 (Delhi, Mumbai, Chennai, Kolkata)

2. SUM()

SUM() adds up all the values in a numeric column. NULL values are ignored.

SELECT SUM(Fees) AS TotalFeesCollected FROM Students;

Result:

SUM With GROUP BY

SELECT Course, SUM(Fees) AS TotalFees
FROM Students
GROUP BY Course;

Result:

3. AVG()

AVG() calculates the arithmetic mean (average) of all non-NULL values in a column.

SELECT AVG(Fees) AS AverageFees FROM Students;

Result: 47428.57 (total 332000 divided by 7 students)

SELECT AVG(Age) AS AverageAge FROM Students;

Result: AVG(Age) uses only 6 rows (Karan's NULL age is ignored): (20+22+19+21+23+20) / 6 = 20.83

4. MIN()

MIN() returns the smallest value in a column. For text columns, it returns the first value alphabetically.

SELECT MIN(Fees) AS LowestFee FROM Students;

Result: 38000 (Arjun Nair)

SELECT MIN(StudentName) AS FirstAlphabetically FROM Students;

Result: Arjun Nair (A comes first alphabetically)

5. MAX()

MAX() returns the largest value in a column. For text columns, it returns the last value alphabetically.

SELECT MAX(Fees) AS HighestFee FROM Students;

Result: 60000 (Rohit Das)

Using Multiple Aggregate Functions Together

SELECT
    COUNT(*)        AS TotalStudents,
    SUM(Fees)       AS TotalFees,
    AVG(Fees)       AS AverageFee,
    MIN(Fees)       AS LowestFee,
    MAX(Fees)       AS HighestFee
FROM Students;

Result:

Aggregate Functions With GROUP BY

SELECT City,
       COUNT(*)   AS Students,
       SUM(Fees)  AS TotalFees,
       AVG(Fees)  AS AvgFee,
       MIN(Age)   AS YoungestAge,
       MAX(Age)   AS OldestAge
FROM Students
GROUP BY City;

Result:

How Aggregate Functions Handle NULL

Key Points to Remember

• Aggregate functions return a single value summarizing multiple rows.
• All aggregate functions except COUNT(*) ignore NULL values.
• They are almost always used with GROUP BY to get per-group summaries.
• They can be used in the HAVING clause to filter groups.
• Always give aggregated columns a meaningful alias using AS for clear output.

Summary

SQL's five core aggregate functions — COUNT(), SUM(), AVG(), MIN(), and MAX() — are essential tools for data analysis and reporting. They convert a group of rows into a single summary value. When combined with GROUP BY, they provide per-group insights. They form the backbone of every analytical SQL query.