Which of the following SQL queries correctly counts the number of orders where payment_status = 'paid' using conditional aggregation?

easy📝 Syntax Q3 of 15

SQL - Advanced Query Patterns

Which of the following SQL queries correctly counts the number of orders where payment_status = 'paid' using conditional aggregation?

ASELECT COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) FROM orders;

BSELECT COUNT(CASE payment_status = 'paid' THEN 1 ELSE 0 END) FROM orders;

CSELECT SUM(CASE WHEN payment_status = 'paid' THEN 0 ELSE 1 END) FROM orders;

DSELECT COUNT(*) WHERE payment_status = 'paid' FROM orders;

Step-by-Step Solution

Solution:

Step 1: Understand COUNT with CASE
COUNT counts non-null values, so using CASE WHEN condition THEN 1 END returns 1 for matching rows and NULL otherwise.
Step 2: Analyze each option
SELECT COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) FROM orders; correctly uses COUNT with CASE WHEN payment_status = 'paid' THEN 1 END, counting only paid orders.
SELECT COUNT(CASE payment_status = 'paid' THEN 1 ELSE 0 END) FROM orders; has incorrect CASE syntax.
SELECT SUM(CASE WHEN payment_status = 'paid' THEN 0 ELSE 1 END) FROM orders; sums 0 for paid, which is incorrect.
SELECT COUNT(*) WHERE payment_status = 'paid' FROM orders; has invalid SQL syntax.
Final Answer:
Option A -> Option A
Quick Check:
COUNT with CASE WHEN condition THEN 1 END counts matching rows [OK]

Quick Trick: Use COUNT with CASE WHEN condition THEN 1 END [OK]

Common Mistakes:

Master "Advanced Query Patterns" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes