Which two statements are true about aggregate functions?

Question

Options

• ASUM () returns o if there are no rows to aggregate.
• BMAX () returns null if there are no rows to aggregate.
• CCOUNT (distinct) returns a count of the number of rows with different values including Null.
• DMIN () cannot use distinct when it executes as a Windows function.
• EAVG () does not allow use of the distinct option.

• B
  4% (1)
• C
  8% (2)
• E
  88% (21)

Explanation

Note: There appears to be an error in the provided answer key. Based on standard SQL behavior, E is actually false - AVG(DISTINCT column) is a valid and commonly supported syntax across Oracle, MySQL, PostgreSQL, and SQL Server. The two statements that are actually true are B and D.

B is true: MAX() (and MIN(), SUM(), AVG()) return NULL - not zero - when there are no rows to aggregate. Only COUNT() returns 0 for an empty set.

D is true: Aggregate functions used as window functions (with an OVER clause) do not support the DISTINCT keyword. Attempting MIN(DISTINCT col) OVER (...) will produce an error.

Why the distractors are wrong:

• A is false - SUM() returns NULL, not 0, on an empty set.
• C is false - COUNT(DISTINCT) excludes NULL values, just like plain DISTINCT.
• E is false - AVG(DISTINCT col) is valid SQL; it averages only the unique non-null values.

Memory tip: Think "NULLs on empty, no DISTINCT in windows." All aggregate functions return NULL when no rows qualify, and DISTINCT is never allowed inside a window function (OVER clause).

No community discussion yet for this question.