SQL Aggregate functions

SQL Aggregate Functions are used to perform calculations on a set of rows and return a single value. These functions are particularly useful when we need to summarize, analyze or group large datasets in SQL databases.

They are often used with GROUP BY clause in SQL to summarize data for each group. Commonly used aggregate functions include COUNT(), SUM(), AVG(), MIN() and MAX().

Key Features of Aggregate Functions

• Operate on groups of rows: They work on a set of rows and return a single value.
• Ignore NULLs: Most aggregate functions ignore NULL values, except for COUNT(*).
• Used with GROUP BY: To perform calculations on grouped data, often use aggregate functions with GROUP BY.
• Can be combined with other SQL clauses: Aggregate functions can be used alongside HAVING, ORDER BY and other SQL clauses to filter or sort results.

Commonly Used SQL Aggregate Functions

Below are the most frequently used aggregate functions in SQL.

1. Count()

It is used to count the number of rows in a table. It helps summarize data by giving the total number of entries. It can be used in different ways depending on what you want to count:

• COUNT(*): Counts all rows.
• COUNT(column_name): Counts non-NULL values in the specified column.
• COUNT(DISTINCT column_name): Counts unique non-NULL values in the column.

Examples:

-- Total number of records in the table
SELECT COUNT(*) AS TotalRecords FROM Employee;

-- Count of non-NULL salaries
SELECT COUNT(Salary) AS NonNullSalaries FROM Employee;

-- Count of unique non-NULL salaries
SELECT COUNT(DISTINCT Salary) AS UniqueSalaries FROM Employee;

2. SUM()

It is used to calculate the total of a numeric column. It adds up all non-NULL values in that column for Example, SUM(column_name) returns sum of all non-NULL values in the specified column.

Examples:

-- Calculate the total salary
SELECT SUM(Salary) AS TotalSalary FROM Employee;

-- Calculate the sum of unique salaries
SELECT SUM(DISTINCT Salary) AS DistinctSalarySum FROM Employee;

3. AVG()

It is used to calculate average value of a numeric column. It divides sum of all non-NULL values by the number of non-NULL rows for Example, AVG(column_name) returns average of all non-NULL values in the specified column.

Examples:

-- Calculate the average salary
SELECT AVG(Salary) AS AverageSalary FROM Employee;

-- Average of distinct salaries
SELECT AVG(DISTINCT Salary) AS DistinctAvgSalary FROM Employee;

4. MIN() and MAX()

The MIN() and MAX() functions return the smallest and largest values, respectively, from a column.

Examples:

-- Find the highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employee;

-- Find the lowest salary
SELECT MIN(Salary) AS LowestSalary FROM Employee;

Examples Queries

Let's consider a demo Employee table to demonstrate SQL aggregate functions. This table contains employee details such as their ID, Name and Salary.

1. Count Total Number of Employees

SELECT COUNT(*) AS TotalEmployees FROM Employee;

Output:

2. Calculate Total Salary

SELECT SUM(Salary) AS TotalSalary FROM Employee;

Output:

3. Find Average Salary

SELECT AVG(Salary) AS AverageSalary FROM Employee;

Output:

4. Find Highest and Lowest Salary

SELECT MAX(Salary) AS HighestSalary FROM Employee;

Output:

Aggregate Functions and GROUP BY Statement in SQL

Visit Course