Aggregate with NULL handling in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What happens to NULL values when using the COUNT(column) aggregate function in SQL?

COUNT(column) counts only non-NULL values in the specified column. NULL values are ignored.

beginner

How does the SUM() function treat NULL values in SQL?

SUM() ignores NULL values and adds only the non-NULL numeric values.

intermediate

What is the difference between COUNT(*) and COUNT(column) in handling NULLs?

COUNT(*) counts all rows regardless of NULLs, while COUNT(column) counts only rows where the column is NOT NULL.

intermediate

How can you include NULL values as zero in aggregate calculations like SUM or AVG?

Use COALESCE(column, 0) to replace NULLs with zero before aggregation, e.g., SUM(COALESCE(column, 0)).

advanced

Why is it important to handle NULLs explicitly in aggregate queries?

Because NULLs can cause aggregates like AVG or SUM to return unexpected results or skip data, handling NULLs ensures accurate calculations.

Which aggregate function counts all rows including those with NULL values in any column?

A COUNT(*)

B COUNT(column)

C SUM(column)

D AVG(column)

COUNT(*) counts every row regardless of NULLs in any column.

What does SUM(column) do when the column contains NULL values?

A Returns NULL if any NULL exists

B Ignores NULLs and sums non-NULL values

C Counts NULLs as zero automatically

D Throws an error

SUM ignores NULL values and adds only the non-NULL numbers.

How can you count only the rows where a column is NOT NULL?

A COUNT(*)

B SUM(column)

C COUNT(column)

D AVG(column)

COUNT(column) counts only rows where the column is NOT NULL.

Which SQL function can replace NULL with a specific value before aggregation?

A IFNULL()

B COALESCE()

C NVL()

D All of the above

IFNULL, COALESCE, and NVL are functions used in different SQL dialects to replace NULLs.

What will AVG(column) return if all values in the column are NULL?

A NULL

B 0

C Error

D 1

AVG returns NULL if there are no non-NULL values to average.

Explain how NULL values affect aggregate functions like COUNT, SUM, and AVG in SQL.

Think about which aggregates count or sum NULLs and which do not.

Describe a method to include NULL values as zero in aggregate calculations and why it might be useful.

Consider how to treat missing data as zero for accurate sums or averages.