SQL - Advanced Query Patterns
Which of the following is the correct syntax using COUNT for counting only rows where
status = 'active' using conditional aggregation?Which of the following is the correct syntax using COUNT for counting only rows where status = 'active' using conditional aggregation?
easy📝 Syntax Q12 of 15
Step-by-Step Solution
Solution:
Step 1: Recall correct CASE syntax inside COUNT
The CASE expression must have WHEN condition THEN value ELSE NULL END for COUNT to count only matching rows.Step 2: Check each option
COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) uses correct CASE syntax with ELSE NULL, so COUNT counts only 'active' rows. COUNT(CASE status = 'active' THEN 1 END) misses WHEN keyword. SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) uses SUM, not COUNT. COUNT(IF status = 'active' THEN 1 ELSE 0 END) uses invalid IF syntax.Final Answer:
COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) -> Option AQuick Check:
COUNT + CASE WHEN ... THEN 1 ELSE NULL END [OK]
Quick Trick: Use ELSE NULL inside CASE for COUNT to skip rows [OK]
Common Mistakes:
- Omitting WHEN keyword in CASE
- Using SUM instead of COUNT for counting rows
- Using ELSE 0 which counts zeros as rows
Master "Advanced Query Patterns" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes