SQL Aggregate Functions

SQL Aggregate Functions

An aggregate function allows you to perform a calculation on a set of values to return a single scalar value. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

The following are the most commonly used SQL aggregate functions:

1. AVG – calculates the average of a set of values.

2. COUNT – counts rows in a specified table or view.

3. MIN – gets the minimum value in a set of values.

4. MAX – gets the maximum value in a set of values.

5. SUM – calculates the sum of values.

All aggregate functions above ignore NULL values except for the COUNT function.

AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Syntax:

AVG (expression)  

Example:

SELECT AVG(COST)  FROM PRODUCT; 

COUNT function

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

Syntax:

COUNT(*) 

or 

COUNT( expression ) 

Example:

SELECT COUNT(*)  FROM PRODUCT;  

MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Syntax:

MIN( expression ) 

Example:

SELECT MIN(RATE) FROM PRODUCT; 

MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Syntax:

MAX( expression )  

Example:

SELECT MAX(RATE) FROM PRODUCT;  

SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

SUM( expression )  

Example:

SELECT SUM(COST) FROM PRODUCT;