COUNT(EXPR)WHEN COUNT(*) IS INVOKED, ALL ROWS IN THE GROUP, INCLUDI...

4. COUNT(expr)

When COUNT(*) is invoked, all rows in the group, including those with nulls or

duplicate values, are counted. When COUNT(DISTINCT expr) is executed, only

unique occurrences of expr are counted for each group. The ALL keyword is part of the

default syntax, so COUNT(ALL expr) and COUNT(expr) are equivalent. If expr is based

on named columns, then nulls are ignored, but if expr is based on anything else, it will

be evaluated for every row, whether there are null values in the row or not. The data

type of expr may be NUMBER, DATE, CHAR, or VARCHAR2. Consider these queries:

Query 1: select count(*) from employees;

Query 2: select count(commission_pct), count(1) from employees;

Query 3: select count(distinct commission_pct) from employees;

Query 4: select count(hire_date), count(manager_id) from

employees;

Query 1 counts the rows in the EMPLOYEES table and returns the integer 107.

Query 2 counts the rows with non-null COMMISSION_PCT values and returns 35.

It also counts the literal expression 1, which is not based on a named column and is

therefore evaluated for every row, returning 107. Query 3 considers the 35 non-null

rows, determines the number of unique values, and returns 7. Query 4 demonstrates

how the COUNT function is used on both a DATE column and a NUMBER column.

The integers 107 and 106 are returned, since there are 107 non-null HIRE_DATE values

and 106 non-null MANAGER_ID values in the group.

The SUM Function

The SUM function returns the aggregated total of the non-null numeric values in a

group. It has this syntax:

This syntax may be deconstructed into the following forms: