What are the SQL Aggregate Functions? Complete Guide

In this article, we are going to discuss SQL Aggregate Functions using practical examples with syntax.

List Of Aggregate Functions

Below are the commonly used SQL Aggregate Functions.

1. AVG – Returns the average value for the selected column in the table
2. COUNT – Returns the number of rows for the selected column in the table
3. FIRST – Returns the first value for the selected column in the table
4. LAST – Returns the last value for the selected column in the table
5. MAX – Returns the largest value for the selected column in the table
6. MIN – Returns the smallest value for the selected column in the table
7. SUM – Returns the sum value for the selected column in the table.

Now I am going to use the Employee table to work on these aggregate functions.

AVG() Function

Average returns average value of a numeric column

The Syntax for this function is as follows

SELECT AVG(column_name) FROM table_name

Practical Use Of AVG Function

Consider the following Employee Table

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL Query to find Average Salary as Follows

Select AVG(Salary) From Employee;

Result of the above query will be as follows.

AVG(Salary)
8200.40

Count() Function

Here count returns the number of rows present in the employee table either based on some condition or without condition.

The syntax for this function is as follows.

SELECT Count(column_name) FROM table_name

Practical Use Of Count Function

Consider the following Employee Table.

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL query to count number of employee based on certain condition is as follows.

SELECT COUNT(Name) FROM Employee WHERE Salary = 6000.25;

Result of the above query will be

Count(Name)
1

FIRST() Function

First function returns first value of a selected column in the table.

Syntax for FIRST function is as follows

SELECT FIRST(column_name) FROM table_name;

Practical Use Of FIRST Function

Consider the following employee table

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL query for this function will be as follows

SELECT FIRST(Salary) FROM Employee

and the results will be as follows

FIRST(Salary)
9000.50

LAST() Function

Last function returns the last value of the selected column in the table.

Syntax for the last function is as follows

Consider the following employee table

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL Query for this function is as follows

SELECT LAST(Salary) FROM Employee

Result of the above query will be as follows.

LAST(Salary)
8000.15

MAX() Function

Max function returns the maximum value of the selected column for a particular table.

Syntax for this function is as follows,

SELECT MAX(column_name) FROM table_name;

Practical use of MAX function is as follows

Consider the following employee table for this function

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL query to find the maximum salary from the table is as follows.

SELECT MAX(Salary) FROM Employee

Result of the query will be as follows.

MAX(Salary)
10000.50

MIN() Function

Minimum function returns the minimum value of the selected column in the specific table

Syntax for MIN function is as follows

SELECT MIN(column_name) FROM table_name 

Practical use of MIN Function

Consider the following employee table

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL query to find the minimum salary from the employee table is as follows

SELECT MIN(Salary) FROM Employee

Results for this query will be as follows

MIN(Salary)
6000.25

SUM() Function

SUM function returns the total sum of selected column for a particular table.

Syntax for this function is as follows

SELECT SUM(table_name) FROM column_name

Practical use of SUM function is as follows

Let us consider the following employee table

Employee ID	Name	Age	Salary
100	Ravi	23	9000.50
101	Divya	30	8000.36
102	Siva	35	6000.25
103	Peter	45	10000.75
104	Lavanya	36	8000.15

SQL query to find the SUM of salary from employee table is as follows.

SELECT SUM(Salary) FROM Employee;

Results for the query will be as follows

SUM(Salary)
41002.01

I hope this article is useful to you. Please leave your comment in the comment section below. I am happy to answer your questions.

You can read our other related article Date Functions in SQL