Agreegate Functions- MIN() and MAX(), AVG(), SUM() and COUNT()

There are some functions in SQL having special use and functionality to perform some operations. The aggregate functions perform operation on a set of values and return a single value. The aggregate function ignore all null values. Aggregate functions usually combines with GROUP BY clause.

MIN()

The MIN() function is used to return smallest value of the column selected.

Syntax

SELECT MIN(column_name)
FROM table_name WHERE condition

Example

ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7

Suppose We want to find or select the candidate having less experience among all other candidates

SELECT MIN(Experience) AS Experience
FROM EmployeeDetails

Output

Experience
2

MAX()

The MAX() function is used to return largest value of the column selected.

Syntax

SELECT MAX(column_name)
FROM table_name WHERE condition

Example

ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7

Suppose We want to find or select the candidate having most experience among all other candidates

SELECT MAX(Experience) AS Experience
FROM EmployeeDetails

Output

Experience
7

COUNT()

The COUNT() Function returns the number of Rows Affected by SQL Query that matches the given condition.

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition

Example

ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7

SELECT COUNT(Experience) As Experience
FROM EMployeeDetails

Output

Experience
4

Now look at another example. We Have added a new Row in the EmployeeDetail Table and Will use COUNT() Function on the State Column of the EmployeeDetail Table.

ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7
5	Tarun Sharma	Maharashtra	2017-08-11 10:15:25.000	5

SELECT COUNT(State) As StateCount
FROM EMployeeDetails

Output

StateCount
5

AVG()

The AVG() Function is used to find out the Average value of the perticular nummeric column in SQL Table.

Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition

Example

ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7
5	Tarun Sharma	Maharashtra	2017-08-11 10:15:25.000	5

SELECT AVG(Experience) As AvrageExperience
FROM EmployeeDetails

Output

AvrageExperience
4.200000

SUM()

The SUM() function is used to return the sum of the column of the table. The column should be the integer or numeric column.

Syntax

SELECT SUM(column_name) FROM table_name
WHERE condition

Example

ID	Name	State	JoinDate	Experience
1	Anoop	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Pune	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-07-11 10:03:30.577	2
4	Arjit singh	Punjab	2017-07-11 10:03:30.577	7
5	Tarun Sharma	Maharashtra	2017-08-11 10:15:25.000	5