Summary -

In this topic, we described about the AGGREGATE Functions with detailed example.

AGGREGATE Functions are used to perform calculations on multiple rows of single column and returns a single value. AGGREGATE Functions are used in SELECT statement with GROUP BY and HAVING clauses AGGREGATE Functions ignore NULL values except for COUNT(*) statement. The AGGREGATE Functions that are used mostly in SQL are listed as follows –

Function Description Syntax
AVG ( ) It returns the average value of specified group values. AVG( ) performs on numeric columns only. AVG ( ) does not consider NULL values.it has optional arguments ALL, DISTINCT. ALL is used to take all values in the specified column. If we specify DISTINCT it omit duplicate values. AVG( ) consider all values by default. AVG([ALL|DISTINCT] expression)
COUNT () It returns the total number of rows that matched with the criteria in result set. COUNT( ) has optional arguments ALL, DISTINCT. ALL is used to consider all values in the specified column. If we specify DISTINCT it omit duplicate values. COUNT(*) consider all rows include NULL values also. COUNT([ALL|DISTINCT] expression)
MIN( ) It returns the minimum value from the group of values of specified condition. It consider all values i.e. DISTINCT has no effect on MIN( ). MIN(expression)
MAX( ) It returns the maximum value from the group of values of specified condition. It consider all values i.e. DISTINCT has no effect on MIN( ). MAX(expression)
SUM( ) It returns the sum value of specified group values. SUM( ) performs on numeric columns only. SUM( ) does not consider NULL values.it has optional arguments ALL, DISTINCT. ALL is used to take all values in the specified column. If we specify DISTINCT it omit duplicate values. SUM( ) consider all values by default. SUM([ALL|DISTINCT] expression)

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

AGGREGATE Functions that are used mostly are explained with examples as follows -

Function Example Output
AVG ( ) SELECT AVG(salary) averagesalary FROM employee_details; Now we can get output like as follows –
averagesalary
24111
COUNT() SELECT COUNT(manager_id) withduplicates, COUNT(DISTINCT manager_id) withoutduplicates, COUNT(*) withNULL FROM employee_details; Now we can get output like as follows –
withduplicates withoutduplicates withNULL
7 4 9
MIN() SELECT MIN(salary) minimumsalary FROM employee_details WHERE dept_id = 1000 ; Now we can get output like as follows –
minimumsalary
15000
MAX( ) SELECT dept_id, MAX(salary) maximumsalary FROM employee_details GROUP BY dept_id; Now we can get output like as follows –
dept_id maximumsalary
1000 45000
2000 40000
SUM( ) SELECT SUM(salary) AS A FROM employee_details; Now we can get output like as follows -
A
217000

Other AGGREGATE Functions that are available are listed as follows –

Function Description Syntax
APPROX_COUNT_DISTINCT It returns approximate value of distinct non-NULL values in result set. APPROX_COUNT_DISTINCT(expression)
CHECKSUM_AGG It returns check sum value in result set for the group of values. It performs on numerical columns only. It omits NULL values. CHECKSUM_AGG(expression)
COUNT_BIG It returns number of rows in the group values. It is same as COUNT but return type is different COUNT returns INT datatype, COUNT_BIG returns BIGINT datatype. COUNT_BIG(expression)
GROUPING It returns an integer value i.e. 0 or 1 in result set. It is used to check whether specified expression is aggregated or not. It returns 0 if the expression is aggregated otherwise it returns 0. GROUPING is used in select statement with group by clause. GROUPING(expression) GROUP BY expression;
STDEV It returns statistical standard deviation value in result set based on sample of data. STDEV(expression)
STDEVP It returns statistical standard deviation value in result set based on entire data. STDEVP(expression)
VAR It returns statistical variance value in result set based on sample of data. VAR(expression)
VARP It returns statistical variance value in result set based on entire data. VARP(expression)

Other AGGREGATE Functions that are available are explained with examples as follows –

Function Example Output
APPROX_COUNT_DISTINCT SELECT APPROX_COUNT_DISTINCT(emp_id) AS A FROM employee_details; Now we can get output like as follows –
A
9
CHECKSUM_AGG SELECT CHECKSUM_AGG (salary) AS A FROM employee_details; Now we can get output like as follows -
A
3528
COUNT_BIG SELECT COUNT_BIG(emp_id) AS A FROM employee_details; Now we can get output like as follows –
A
9
GROUPING SELECT GROUPING(dept_id) AS A FROM employee_details GROUP BY dept_id; Now we can get output like as follows –
A
0
0
STDEV SELECT stdev(salary) AS A FROM employee_details; Now we can get output like as follows -
A
11515.6897800831
STDEVP SELECT stdevp(salary) AS A FROM employee_details; Now we can get output like as follows –
A
10857.0964447165
VAR SELECT VAR(salary) AS A FROM employee_details; Now we can get output like as follows –
A
132611111.111111
VARP SELECT VARP(salary) AS A FROM employee_details; Now we can get output like as follows –
A
117876543.209877