SQL AGGREGATE Functions

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