Learn SQL Aggregate Functions

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

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + 9 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">