SQL Aggregate Functions
In this post we will learn basic functions in sql and explain how to use functions with an example.
SQL has many in-built functions for performing processing on data.
There are basic two type of functions in sql.
1. SQL Scalar function
2. SQL Aggregate function
In this post i will explain about SQL Aggregate function.
SQL aggregate functions – The sql aggregate functions returns a single value from values calculated group of rows, rather than on single row.
They basically summarize the results of a particular column of selected data
The SQL AVG() aggregate function :
AVG() – The AVG() function returns average values from a table column.
The syntax of AVG() is :
SELECT AVG(col_name) AS col_name FROM Table_Name
Example of AVG() function :
Consider below sql table Student to understand aggregate function example.
| ID | Name | City | Age | Mobile |
|---|---|---|---|---|
| 1 | Meera | Bombay | 20 | 7874555555 |
| 2 | Rahul | Surat | 25 | 7874444444 |
| 3 | Arav | Ahmedabad | 35 | 7874333333 |
| 4 | Dhvanish | Bombay | 30 | 7874111111 |
| 5 | Dharv | Baroda | 42 | 7874666666 |
| 6 | Veera | Baroda | 28 | 7874121212 |
SELECT AVG(ID) as ID FROM Student
The Result of above query is :
| ID |
|---|
| 3 |
SELECT AVG(AGE) as AverageAGE FROM Student
The Result of above avg() function query is :
| AverageAGE |
|---|
| 30 |
The SQL COUNT() aggregate function :
COUNT() – The COUNT() function used to count the number of row in a Table.
The syntax of COUNT() is :
SELECT COUNT(*) FROM Table_Name
Example of COUNT() function :
SELECT COUNT(ID) as CountStudent FROM Student
| CountStudent |
|---|
| 6 |
SELECT COUNT(AGE) as CountStudent FROM Student WHERE age>27
| CountStudent |
|---|
| 4 |
The SQL FIRST() aggregate function :
FIRST() – The FIRST() function returns the first value from a column.
The syntax of FIRST() is :
SELECT FIRST(col_name) FROM Table_Name
The SQL TOP() aggregate function :
We use TOP() function in SQL Server instead of FIRST() function
The TOP() function SQL Server syntax is :
SELECT TOP(number) col_name FROM Table_Name ORDER BY col_name
SELECT TOP 1 FName FROM StudentMst ORDER BY FName ASC
Example of TOP() function :
SELECT TOP 3 * FROM Student
| ID | Name | City | Age | Mobile |
|---|---|---|---|---|
| 1 | Meera | Bombay | 20 | 7874555555 |
| 2 | Rahul | Surat | 25 | 7874444444 |
| 3 | Arav | Ahmedabad | 35 | 7874333333 |
The SQL LAST() aggregate function :
LAST() – The LAST() function returns the last value from a column.
The syntax of LAST() is :
SELECT LAST(col_name) FROM Table_Name
If you want to get same out put as LAST() function in SQL Server we use TOP() like :
SELECT TOP(number) col_name FROM Table_Name ORDER BY col_name DESC
SELECT TOP 1 FName FROM StudentMst ORDER BY FName DESC
Example of TOP() function :
SELECT TOP 3 * FROM Student ORDER BY ID DESC
| ID | Name | City | Age | Mobile |
|---|---|---|---|---|
| 6 | Veera | Baroda | 28 | 7874121212 |
| 5 | Dharv | Baroda | 42 | 7874666666 |
| 4 | Dhvanish | Bombay | 30 | 7874111111 |
The SQL MAX() aggregate function :
MAX() – The MAX() function returns the largest value from a column.
The syntax of MAX() is :
SELECT MAX(col_name) FROM Table_Name
Example of MAX() function :
SELECT MAX(AGE) as MaximumAge FROM Student
| MaximumAge |
|---|
| 42 |
The SQL MIN() aggregate function :
MIN() – The MIN() function returns the smallest value from a column.
The syntax of MIN() is :
SELECT MIN(col_name) FROM Table_Name
Example of MIN() function :
SELECT MIN(AGE) as MinimumAge FROM Student
| MinimumAge |
|---|
| 20 |
The SQL SUM() aggregate function :
SUM() – The SUM() function returns the total sum of values from a column.
The syntax of SUM() is :
SELECT SUM(col_name) FROM Table_Name
Example of SUM() function :
SELECT SUM(AGE) as AgeSum FROM Student
| AgeSum |
|---|
| 180 |