Aggregate Functions
An aggregate function performs a calculation on a set of values, and returns a
single value. Except for COUNT(*), aggregate functions ignore null values.
Aggregate functions are often used with the GROUP BY clause of the SELECT
statement.
All aggregate functions are deterministic. In other words, aggregate functions
return the same value each time that they are called, when called with a specific set
of input values. The OVER clause may follow all aggregate functions, except the
STRING_AGG, GROUPING or GROUPING_ID functions .
Use aggregate functions as expressions only in the following situations:
• The select list of a SELECT statement (either a subquery or an outer query).
• A HAVING clause.
The main following aggregate functions used in sql are:
COUNT FUNCTION
o 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.
o 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( [ALL|DISTINCT] expression )
Sample table:
PRODUCT_MAST
PRODUCT COMPANY QTY RATE
Item1 Com1 2 10
Item2 Com2 3 25
Item3 Com1 2 30
Item4 Com3 5 10
Item5 Com2 2 20
Item6 Cpm1 3 25
Item7 Com1 5 30
Item8 Com1 3 10
Item9 Com2 2 25
Item10 Com3 4 30
Example: COUNT()
SELECT COUNT(*)
FROM PRODUCT_MAST;
Output:
10
Example: COUNT with WHERE
SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;
Output:
Example: COUNT() with DISTINCT
SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;
Output:
Example: COUNT() with GROUP BY
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
Output:
Com1 5
Com2 3
Com3 2
Example: COUNT() with HAVING
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>2;
Output:
Com1 5
Com2 3
SUM Function
Sum function is used to calculate the sum of all selected columns. It works on
numeric fields only.
Syntax
SUM()
or
SUM( [ALL|DISTINCT] expression )
Example: SUM()
SELECT SUM(COST)
FROM PRODUCT_MAST;
Output:
670
Example: SUM() with WHERE
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;
Output:
320
Example: SUM() with GROUP BY
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;
Output:
Com1 150
Com2 170
Example: SUM() with HAVING
SELECT COMPANY, SUM(COST)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING SUM(COST)>=170;
Output:
Com1 335
Com3 170
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()
or
AVG( [ALL|DISTINCT] expression )
Example:
SELECT AVG(COST)
FROM PRODUCT_MAST;
Output:
67.00
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()
or
MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(RATE)
FROM PRODUCT_MAST;
Output
30
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()
or
MIN( [ALL|DISTINCT] expression )
Example:
SELECT MIN(RATE)
FROM PRODUCT_MAST;
Output:
10
