SQL Aggregate Functions

What is aggregate functions?

  • Aggregate functions perform a calculation on a set of values and return a single value.
  • Aggregate functions ignore NULL values except COUNT.
  • It is used with the GROUP BY clause of the SELECT statement.
Following are the Aggregate functions:
1. AVG
2. MAX
3. MIN
4. SUM
5. COUNT()
6. COUNT(*)

Example

<Employee> Table

EidEnameAgeCitySalary
E001ABC29Pune20000
E002PQR30Pune30000
E003LMN25Mumbai5000
E004XYZ24Mumbai4000
E005STU32Bangalore25000

Aggregate FunctionsDescriptionSyntaxExampleOutput
AVGIt returns the average of the data values.SELECT AVG
<column_name>
FROM
<table_name>;		SELECT AVG(Salary)
FROM Employee;		AVG(Salary)
16800
MAXIt returns the maximum value for a column.SELECT MAX
<column_name>
FROM
<table_name>;		SELECT MAX(Salary)
FROM Employee;		MAX(Salary)
30000
MINIt returns the minimum value for a column.SELECT MIN
<column_name>
FROM
<table_name>;		SELECT MIN(Salary)
FROM Employee;		MIN(Salary)
4000
SUMIt returns the sum (addition) of the data values.SELECT SUM
<column_name>
FROM
<table_name>;		SELECT SUM(Salary)
FROM Employee
WHERE City='Pune';		SUM(Salary)
50000
COUNT()It returns total number of values in a given column.SELECT COUNT
<column_name>
FROM
<table_name>;		SELECT COUNT(Empid)
FROM Employee;		COUNT(Empid)
5
COUNT(*)It returns the number of rows in a table.SELECT COUNT(*)
FROM
<table_name>;		SELECT COUNT(*)
FROM Employee;		COUNT(*)
5
▲