In SQL Server it provides several inbuilt functions but SQL Server provide five types of inbuilt function to perform mathematical operations on result set.

SQL Mathematical Functions are.
1) COUNT : Use to count the number of rows in selected result set.
2) MIN : Use to find out minimum value from selected result set.
3) MAX : Use to find out maximum value from selected result set.
4) SUM : Use to calculate sum of value from selected result set.
5) AVG : Use to calculate average of value from selected result set.


Note : All above are mathematical functions so it return scalar (single) value.
We can use these mathematical functions only on numerical type columns.


Let consider below list of student with there class, marks, city and contact details.

select statement for Aggregate functions


COUNT in SQL Server

Find out the count of student who belong from Pune city.
Let see below example.

Example

--We can use any one statement out of it
SELECT COUNT (*) AS 'StudentCount' FROM tblStudent WHERE City = 'Pune'
--OR
SELECT COUNT (StudentName) AS 'StudentCount' FROM tblStudent WHERE City = 'Pune'

Output

count Aggregate functions example in sql server


MIN in SQL Server

Find out the minimum marks of student who belong from Pune city.
Let see below example.

Example

SELECT MIN (Marks) AS 'MinimumMarks' FROM tblStudent WHERE City = 'Mumbai'

Output

min Aggregate functions example in sql server


MAX in SQL Server

Find out the maximum marks of student who belong from Goa city.
Let see below example.

Example

SELECT MAX (Marks) AS 'MaximumMarks' FROM tblStudent WHERE City = 'Goa'

Output

max Aggregate functions example in sql server


SUM in SQL Server

Find out the sum of marks of student who belong from Mumbai city.
Let see below example.

Example

SELECT SUM (Marks) AS 'SUMOfMarks' FROM tblStudent WHERE City = 'Mumbai'

Output

SUM Aggregate functions example in sql server


AVG in SQL Server

Find out the sum of marks of student who belong from Mumbai city.
Let see below example.

Example

SELECT AVG (Marks) AS 'AVGOfMarks' FROM tblStudent WHERE City = 'Mumbai'

Output

avg Aggregate functions example in sql server