1Z0-909 practice question 5 of 15

Which two statements are true about aggregate functions?

Full set: 65 questions
Answer options
A: SUM () returns o if there are no rows to aggregate.
B: MAX () returns null if there are no rows to aggregate. Correct answer
C: COUNT (distinct) returns a count of the number of rows with different values including Null.
D: MIN () cannot use distinct when it executes as a Windows function. Correct answer
E: AVG () does not allow use of the distinct option.
Correct Answer:
B. MAX () returns null if there are no rows to aggregate.; D. MIN () cannot use distinct when it executes as a Windows function.
Explanation

In MySQL 8.0 aggregate functions return NULL when the query produces no rows; this is explicitly stated for MAX(). COUNT(DISTINCT) excludes NULLs, while AVG() and SUM() accept the DISTINCT modifier in ordinary aggregation. When an aggregate is used as a window function (MIN() … OVER()), the DISTINCT keyword is forbidden by syntax rules. Therefore, only “MAX() returns NULL if there are no rows” and “MIN() cannot use DISTINCT when it executes as a window function” are true.

Why Incorrect

A. SUM() returns NULL—not 0—when the result set is empty.
C. COUNT(DISTINCT) ignores NULL values; they are not included in the count.
E. AVG() syntax permits the DISTINCT keyword in normal (non-window) aggregation.

References

1. MySQL 8.0 Reference Manual, 12.20 Aggregate (GROUP BY) Functions – “If there are no matching rows, the result is NULL for functions such as SUM(), AVG(), MAX(), MIN().” (Oracle, 2023).

2. MySQL 8.0 Reference Manual, 12.20.2 COUNT() Aggregate Function – “COUNT(DISTINCT expr) returns the number of distinct non-NULL values.”

3. MySQL 8.0 Reference Manual, 12.21.1 Window Function Descriptions – “DISTINCT and ORDER BY are not permitted within the argument list of an aggregate function when it is used as a window function.”

Scroll to Top