SQL Aggregate Functions

SQL aggregate functions are used to perform calculations on a group of rows and return a single result. They help summarize database data and make it easier to analyze large amounts of information.

For example, if a student table contains marks for many students, we can use aggregate functions to calculate the average marks, find the highest score, or count the total number of students.

SQL provides several aggregate functions, including COUNT(), SUM(), AVG(), MIN(), and MAX(). These functions are commonly used with the SELECT statement to retrieve useful information from database tables.

Types of SQL Aggregate Functions

The following table describes the most commonly used SQL aggregate functions.

Example of SQL Aggregate Functions

Consider the following Employees table:

We can use aggregate functions to count employees, calculate the total and average salaries, and find the highest and lowest salaries.

1. COUNT() Function

The COUNT() function is used to count the total number of rows in a table or the number of non-NULL values in a specific column. It is useful for finding the number of records in a table or the number of entries that contain a value in a specific column. COUNT(*) counts all rows, while COUNT(column_name) counts only non-NULL values in the specified column.

Syntax:

SELECT COUNT(column_name) FROM table_name;

Example:

SELECT COUNT(*) AS TotalEmployees FROM Employees;

Output:

Explanation:

In this example, COUNT(*) counts all rows in the Employees table and returns 5 because the table contains five employee records. The AS TotalEmployees alias gives the result column a meaningful name.

2. SUM() Function

The SUM() function is used to calculate the total of all non-NULL numeric values in a column. It is commonly used to calculate total salaries, sales amounts, expenses, and other numeric values stored in a database.

Syntax:

SELECT SUM(column_name) FROM table_name;

Example:

SELECT SUM(Salary) AS TotalSalary FROM Employees;

Output:

Explanation:

Here, SUM(Salary) adds the salaries of all employees and returns 180000. The TotalSalary alias identifies the result as the combined salary of all employees.

3. AVG() Function

The AVG() function calculates the average of all non-NULL numeric values in a column. It is useful for finding average salaries, student marks, product prices, and other numerical measurements. The function calculates the average by dividing the sum of the non-NULL values by their count.

Syntax:

SELECT AVG(column_name) FROM table_name;

Example:

SELECT AVG(Salary) AS AverageSalary FROM Employees;

Output:

Explanation:

In this example, AVG(Salary) calculates the average salary of the five employees. Their total salary is 180000, so the average salary is 36000.

4. MIN() Function

The MIN() function returns the smallest value in a column, ignoring NULL values. It is useful for finding the lowest salary, the cheapest product, the earliest date, or the minimum value in a set of records.

Syntax:

SELECT MIN(column_name) FROM table_name;

Example:

SELECT MIN(Salary) AS LowestSalary FROM Employees;

Output:

Explanation:

Here, MIN(Salary) checks the salary values in the Employees table and returns 25000, which is the lowest salary recorded in the table.

5. MAX() Function

The MAX() function returns the largest value in a column, ignoring NULL values. It is commonly used to find the highest salary, the most expensive product, the latest date, or the maximum value among multiple records.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example:

SELECT MAX(Salary) AS HighestSalary FROM Employees;

Output:

Explanation:

In this example, MAX(Salary) compares the salary values and returns 50000, which is the highest salary in the Employees table.

Using Aggregate Functions with GROUP BY

The GROUP BY clause is used with aggregate functions to calculate results for different groups of rows. For example, a company can calculate the total salary paid to employees in each department.

Suppose the Employees table contains a Department column. The following query calculates the total salary for each department.

SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department;

This query groups employees by department and calculates the total salary for each group. Each department appears in the result with its combined salary.

Important Points About SQL Aggregate Functions

• Aggregate functions summarize data from multiple rows.
• Most aggregate functions ignore NULL values. However, COUNT(*) counts all rows, including rows containing NULL values.
• COUNT(*) counts rows, while COUNT(column_name) counts non-NULL values in the specified column.
• SUM() and AVG() are generally used with numeric columns.
• MIN() and MAX() can work with numeric, date, and text values, depending on the database system and data type.
• Aggregate functions can be used with GROUP BY to calculate results for different groups.
• The HAVING clause can filter groups based on aggregate results.

Conclusion

SQL aggregate functions help summarize and analyze database data. The COUNT(), SUM(), AVG(), MIN(), and MAX() functions are used to count records, calculate totals and averages, and find the smallest and largest values. They are useful for analyzing employee records, student marks, sales figures, and other types of data.