MySQL Aggregate Functions

Aggregate functions perform a calculation on a collection of rows and return a summarized result.

SELECT COUNT(*)
FROM students;

This query counts the total number of rows in the students table.

The five most commonly used aggregate functions are:

COUNT(*) counts all rows in a table.

SELECT COUNT(*)
FROM students;

If the table contains 100 rows, the result will be:

100

COUNT(*) counts rows regardless of whether individual columns contain NULL values.

You can count the non-NULL values of a particular column.

SELECT COUNT(email)
FROM students;

If some students have NULL in the email column, those NULL values are not counted.

COUNT(DISTINCT column) counts the number of different non-NULL values in a column.

SELECT COUNT(DISTINCT course)
FROM students;

If students are enrolled in Python, Java, PHP, and Python again, the distinct course count is:

3

The SUM() function calculates the total of numeric values.

SELECT SUM(fee)
FROM students;

Example data:

5000
7000
8000

Result:

20000

You can calculate the total only for rows that match a condition.

SELECT SUM(fee)
FROM students
WHERE course = 'Python';

This calculates the total fee for Python students only.

The AVG() function calculates the average of non-NULL numeric values.

SELECT AVG(fee)
FROM students;

If the fees are:

10000
15000
20000

The average is:

15000

The result of AVG() may contain decimal values. You can use ROUND() to format it.

SELECT ROUND(AVG(fee), 2)
AS average_fee
FROM students;

This displays the average fee up to two decimal places.

The MAX() function returns the largest value.

SELECT MAX(fee)
FROM students;

If the fees are:

10000
15000
25000
18000

Result:

25000

The MIN() function returns the smallest value.

SELECT MIN(fee)
FROM students;

If the fees are:

10000
15000
25000
18000

Result:

10000

You can use multiple aggregate functions in the same SELECT statement.

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(fee) AS average_fee,
    MAX(fee) AS highest_fee,
    MIN(fee) AS lowest_fee
FROM students;

This query produces a useful summary of the students table.

The WHERE clause filters rows before the aggregate function calculates the result.

SELECT COUNT(*)
FROM students
WHERE course = 'Python';

This counts only students enrolled in Python.

SUM() is commonly used in payment and fee reports.

SELECT SUM(amount) AS total_collection
FROM payments;

This calculates the total amount collected from all selected payment records.

You can combine SUM() with date conditions.

SELECT SUM(amount) AS september_collection
FROM payments
WHERE payment_date >= '2026-09-01'
AND payment_date < '2026-10-01';

This calculates the total collection during September 2026.

COUNT() can be used to find the number of students.

SELECT COUNT(*) AS total_students
FROM students;

You can also count active students:

SELECT COUNT(*) AS active_students
FROM students
WHERE status = 'Active';

Most aggregate functions ignore NULL values.

SELECT AVG(marks)
FROM results;

If some rows have NULL marks, AVG() calculates the average using the non-NULL marks.

If SUM() has no matching non-NULL values, its result can be NULL.

You can use IFNULL() to return 0 instead.

SELECT IFNULL(SUM(amount), 0)
AS total_amount
FROM payments
WHERE student_id = 1001;

If there are no matching payment values, the displayed result becomes:

0

Aliases make aggregate results easier to understand.

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee
FROM students;

Here:

• total_students is the alias for COUNT(*)
• total_fee is the alias for SUM(fee)

Aggregate functions are often used with GROUP BY.

SELECT
    course,
    COUNT(*) AS total_students
FROM students
GROUP BY course;

This returns the number of students in each course.

GROUP BY will be covered in detail in the next lesson.

You can calculate totals separately for different groups.

SELECT
    course,
    SUM(fee) AS total_fee
FROM students
GROUP BY course;

This calculates the total fee for each course.

You can calculate the average value for each group.

SELECT
    course,
    ROUND(AVG(fee), 2) AS average_fee
FROM students
GROUP BY course;

This calculates the average fee separately for each course.

MAX() can find the highest value in every group.

SELECT
    course,
    MAX(fee) AS highest_fee
FROM students
GROUP BY course;

This finds the highest fee in each course.

MIN() can find the lowest value in every group.

SELECT
    course,
    MIN(fee) AS lowest_fee
FROM students
GROUP BY course;

This finds the lowest fee in each course.

DISTINCT can be used inside some aggregate functions to calculate results using unique values.

SELECT COUNT(DISTINCT course)
AS total_courses
FROM students;

You can also use:

SELECT SUM(DISTINCT fee)
FROM students;

This adds each distinct non-NULL fee value only once.

There is an important difference between COUNT(*) and COUNT(column).

Example:

SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS students_with_email
FROM students;

Aggregate functions are useful for analyzing student marks.

SELECT
    COUNT(*) AS total_students,
    ROUND(AVG(marks), 2) AS average_marks,
    MAX(marks) AS highest_marks,
    MIN(marks) AS lowest_marks
FROM results;

This query creates a simple examination summary.

Suppose a students table contains total_fee and paid_fee.

SELECT
    SUM(total_fee) AS total_fee,
    SUM(paid_fee) AS total_paid,
    SUM(total_fee - paid_fee) AS total_due
FROM students;

This query can provide a basic fee summary.

You can also round calculated values:

SELECT
    ROUND(SUM(total_fee), 2) AS total_fee,
    ROUND(SUM(paid_fee), 2) AS total_paid
FROM students;

These functions are especially useful when creating dashboards and reports.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100),
    fee DECIMAL(10,2),
    marks INT
);

INSERT INTO students
(name, course, fee, marks)
VALUES
('Amit Kumar', 'Python', 15000, 85),
('Priya Singh', 'Java', 18000, 92),
('Rahul Sharma', 'Python', 12000, 78),
('Neha Kumari', 'PHP', 10000, 88),
('Ravi Kumar', 'Python', 15000, 81);

-- Count students
SELECT COUNT(*) AS total_students
FROM students;

-- Total fee
SELECT SUM(fee) AS total_fee
FROM students;

-- Average fee
SELECT ROUND(AVG(fee), 2) AS average_fee
FROM students;

-- Highest marks
SELECT MAX(marks) AS highest_marks
FROM students;

-- Lowest marks
SELECT MIN(marks) AS lowest_marks
FROM students;

-- Complete summary
SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    ROUND(AVG(fee), 2) AS average_fee,
    MAX(marks) AS highest_marks,
    MIN(marks) AS lowest_marks
FROM students;

-- Course-wise summary
SELECT
    course,
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY course;

This example demonstrates how aggregate functions can be used to create useful student, fee, and marks reports.