In DB2 there are five aggregate functions. The general thumb rule is aggregate functions can be applied on group of column values. The list is COUNT,SUM, AVG, MAX and MIN.These are typical functions being used across all the projects.
The best example for COUNT:
EXEC SQL SELECT COUNT(*)
INTO :FEMALE FROM DSN8A10.EMP
WHERE SEX = 'F';
Read the specific behaviour of “COUNT function” vs other aggregate functions.
Additional points on COUNT function
- When no rows are present in the column COUNT functions returns zero. But all other functions returns NULL.
The best example for SUM:
Read full article about “SUM function” here.
The best example for AVG, MAX, MIN
SELECT DEPT, AVG(SALARY), MAX(SALARY), MIN(SALARY) INTO :WS-DEPT, :WS-AVG, :WS-MAX, :WS-MIN FROM TEST.EMPLOYEES;
Two typical points to note on aggregate functions:
- If a GROUP BY clause is specified in a query and the intermediate result from the FROM, WHERE, GROUP BY, and HAVING clauses is the empty set, then the aggregate functions are not applied and the result of the query is the empty set.
- If the GROUP BY clause is not specified in a query and the intermediate result table of the FROM, WHERE, and HAVING clauses is the empty set, then the aggregate functions are applied to the empty set.
- This is not allowed using one aggregate function in another aggregate function
Related: You can Try “Certificate in SQL”, a best way to prove your skills