You want to find the average rating from a reviews table where the rating column has NULLs and zeros. You want to exclude NULLs but include zeros in the average. Which query is correct?

hard📝 Application Q9 of 15

SQL - Aggregate Functions

You want to find the average rating from a reviews table where the rating column has NULLs and zeros. You want to exclude NULLs but include zeros in the average. Which query is correct?

ASELECT AVG(rating) FROM reviews WHERE rating != 0;

BSELECT AVG(rating) FROM reviews WHERE rating IS NOT NULL;

CSELECT AVG(COALESCE(rating, 0)) FROM reviews;

DSELECT AVG(NULLIF(rating, 0)) FROM reviews;

Step-by-Step Solution

Solution:

Step 1: Exclude NULLs but keep zeros
WHERE rating IS NOT NULL excludes NULLs but keeps zeros.
Step 2: AVG calculates average of non-NULL ratings including zeros
AVG(rating) averages all non-NULL values including zeros.
Final Answer:
SELECT AVG(rating) FROM reviews WHERE rating IS NOT NULL; -> Option B
Quick Check:
Exclude NULLs with IS NOT NULL, keep zeros in AVG = C [OK]

Quick Trick: Use WHERE IS NOT NULL to exclude NULLs but keep zeros [OK]

Common Mistakes:

MISTAKES

Replacing NULLs with zero causing double zero count
Filtering out zeros instead of NULLs
Using NULLIF to exclude zeros incorrectly

Master "Aggregate Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More SQL Quizzes

You want to find the average rating from a reviews table where the rating column has NULLs and zeros. You want to exclude NULLs but include zeros in the average. Which query is correct?

Step 1: Exclude NULLs but keep zeros

Step 2: AVG calculates average of non-NULL ratings including zeros

Final Answer:

Quick Check:

Want More Practice?