Aggregate Functions in SQL

Aggregate functions perform calculations on a group of rows and return one result.

For example, they can be used to find:

• Total number of students
• Total fees
• Average marks
• Lowest marks
• Highest marks

The most commonly used aggregate functions are:

The COUNT() function counts rows or non-NULL values.

SELECT COUNT(*)
FROM students;

This returns the total number of rows in the students table.

COUNT(*) counts all rows in the result, including rows containing NULL values in individual columns.

SELECT COUNT(*)
FROM students;

This is commonly used to find the total number of records.

COUNT(column_name) counts non-NULL values in the specified column.

SELECT COUNT(mobile)
FROM students;

Rows where mobile is NULL are not counted.

You can combine COUNT with DISTINCT to count unique values.

SELECT COUNT(DISTINCT course)
FROM students;

This counts the number of different courses.

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

SELECT SUM(fee)
FROM students;

This returns the total fee value.

SUM() can be combined with WHERE to calculate a total for selected records.

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

This calculates the total fee for Python students.

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

SELECT AVG(marks)
FROM students;

This returns the average marks.

You can calculate an average for selected records using WHERE.

SELECT AVG(marks)
FROM students
WHERE course = 'SQL';

This calculates the average marks of SQL students.

The MIN() function returns the smallest value in a column.

SELECT MIN(marks)
FROM students;

This returns the lowest marks.

The MAX() function returns the largest value in a column.

SELECT MAX(marks)
FROM students;

This returns the highest marks.

MIN() can be used to find the lowest salary.

SELECT MIN(salary)
FROM employees;

This returns the smallest salary value.

MAX() can be used to find the highest salary.

SELECT MAX(salary)
FROM employees;

This returns the largest salary value.

You can use multiple aggregate functions in one SELECT statement.

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

This returns several summary values in one result.

Aliases make aggregate results easier to understand.

SELECT
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks
FROM students;

The result columns will have meaningful names.

Most aggregate functions ignore NULL values when calculating results.

SELECT AVG(marks)
FROM students;

Rows where marks is NULL are not included in the average calculation.

These two forms behave differently when NULL values exist.

SELECT COUNT(*)
FROM students;

SELECT COUNT(marks)
FROM students;

COUNT(*) counts rows, while COUNT(marks) counts only non-NULL marks.

SUM() generally ignores NULL values when calculating the total.

SELECT SUM(fee)
FROM students;

Only non-NULL fee values contribute to the total.

AVG() generally ignores NULL values when calculating the average.

SELECT AVG(marks)
FROM students;

NULL marks are not treated as zero in the average calculation.

WHERE can filter rows before the aggregate calculation is performed.

SELECT SUM(fee)
FROM students
WHERE status = 'Active';

This calculates the total fee for active students.

You can combine aggregate functions with BETWEEN.

SELECT AVG(marks)
FROM students
WHERE marks BETWEEN 50 AND 100;

This calculates the average of marks within the specified range.

IN can be used to filter records before applying an aggregate function.

SELECT COUNT(*)
FROM students
WHERE course IN ('Python', 'SQL');

This counts students enrolled in Python or SQL.

COUNT() can be used to calculate the number of students with a particular status.

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

This returns the number of active students.

SUM() is useful for calculating total fees collected.

SELECT SUM(amount) AS total_collected
FROM payments
WHERE status = 'Paid';

This calculates the total amount from paid payment records.

MAX() and MIN() can be used together.

SELECT
    MAX(marks) AS highest_marks,
    MIN(marks) AS lowest_marks
FROM students;

This returns both the highest and lowest marks.

A common mistake is expecting an aggregate function to return individual rows.

SELECT AVG(marks)
FROM students;

This returns one average value, not the marks of every student.

Aggregate functions become especially useful with GROUP BY. They can calculate separate results for each group.

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

This counts students separately for each course.

Suppose we have a students table containing marks and fees. We can generate a summary report using aggregate functions.

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(marks) AS average_marks,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students;

This produces a useful summary of the student data.

Here is a complete example using the main aggregate functions.

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

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(marks) AS average_marks,
    MIN(marks) AS lowest_marks,
    MAX(marks) AS highest_marks
FROM students;

The query generates a summary containing the number of students, total fees, average marks, lowest marks, and highest marks.