SQL Aggregate Vs Group By

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 DSN8A10.EMP
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.

Certification

Here are details “Certificate in SQL“.