SQL Aggregate Functions
There are five aggregate functions in SQL.
Top Aggregate Functions
Those are COUNT, SUM, AVG, MAX and MIN.
SQL Count Function
How to use COUNT best example.
EXEC SQL SELECT COUNT(*)
INTO :FEMALE FROM
WHERE SEX = 'F';
Read the specific behaviour of “COUNT function” vs other aggregate functions.
Notes: COUNT function
- When no rows are present in the column, the COUNT function returns zero. But all other functions return NULL.
SQL SUM Function
How to use SUM best example.
Read “SUM function” here.
SQL AVG, MAX, MIN functions
How to use AVG, MAX, MIN best example.
SELECT DEPT, AVG(SALARY), MAX(SALARY), MIN(SALARY)
INTO :WS-DEPT, :WS-AVG, :WS-MAX, :WS-MIN
FROM TEST.EMPLOYEES GROUP BY DEPT;
Group by Vs Aggregate
- If you want to use non aggregate columns, you need to use group by.
- 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.
- Not allowed to use one aggregate function in another aggregate function.
Here are details “Certificate in SQL“.