ALL

invokes the aggregate function for all rows in the group (default)

DISTINCT

invokes the aggregate function for all distinct non-null values of the expression found in the group

expression

(any data type) contains at least one column reference

Query

Result

SELECT COUNT (DISTINCT x)

FROM foo;

the number of distinct values in the x column of table foo

SELECT COUNT (DISTINCT x+y)

FROM foo;

all distinct values of evaluating the expression x+y for all tuples of foo. An equivalent query is:

SELECT COUNT(x+y)

FROM foo

GROUP BY foo;

SELECT x,

COUNT (DISTINCT y)

FROM foo

GROUP BY x;

Each distinct x value in table foo

The number of distinct values of y in all tuples with the specific distinct x value.

SELECT x,

COUNT (DISTINCT x)

FROM foo

GROUP BY x;

Each distinct x value in table foo

The constant "1"

The DISTINCT keyword is redundant if all members of the SELECT list are present in the GROUP BY list as well.

SELECT x,

COUNT (DISTINCT y),

SUM(z)

FROM foo

GROUP BY x;

Each distinct x value

The number of distinct y values for all tuples with the specific x value

The sum of all the z values in all tuples with the specific x value

SELECT x,

COUNT (DISTINCT y),

COUNT (DISTINCT z)

FROM foo

GROUP BY x;

Each distinct x value

The number of distinct y values for all tuples with the specific x value.

The number of distinct z values in all tuples with the specific x value.

SELECT x,

COUNT (DISTINCT y),

COUNT (DISTINCT z),

SUM (q),

COUNT (r)

FROM foo

GROUP BY x;

Each distinct x value

The number of distinct y values for all tuples with the specific x value.

The number of distinct z values in all tuples with the specific x value.

The sum of all q values in tuples with the specific x value

The number of r values in tuples with the specific x value