Bird
0
0

Which of the following can be used to count the number of orders with status 'pending' and 'completed' separately in one query?

hard📝 Application Q9 of 15
PostgreSQL - Aggregate Functions and GROUP BY
Which of the following can be used to count the number of orders with status 'pending' and 'completed' separately in one query?
ASELECT COUNT(*) FILTER (WHERE status = 'pending') AS pending_count, COUNT(*) FILTER (WHERE status = 'completed') AS completed_count FROM orders;
BSELECT COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count, COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count FROM orders;
CSELECT SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count FROM orders;
DAll of the above
Step-by-Step Solution
Solution:

  1. Step 1: Understand FILTER and CASE usage

    FILTER clause can count rows matching a condition. CASE can convert conditions into 1 or 0 for counting or summing.

  2. Step 2: Analyze each option

    SELECT COUNT(*) FILTER (WHERE status = 'pending') AS pending_count, COUNT(*) FILTER (WHERE status = 'completed') AS completed_count FROM orders; uses FILTER correctly. SELECT COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count, COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count FROM orders; counts non-null CASE results. SELECT SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_count FROM orders; sums 1/0 from CASE. All produce correct counts.

  3. Final Answer:

    All of the above -> Option D

  4. Quick Check:

    FILTER and CASE both can count conditionally [OK]
Quick Trick: FILTER and CASE can both count conditionally [OK]
Common Mistakes:
  • Thinking only FILTER or CASE works
  • Confusing COUNT with SUM in CASE
  • Missing ELSE in CASE leading to NULLs

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes
Aggregate Functions and GROUP BY - ROLLUP and CUBE for hierarchical totals - Quiz 15hard JSON and JSONB - Indexing JSONB with GIN - Quiz 2easy JSON and JSONB - Why JSON support matters in PostgreSQL - Quiz 12easy Joins in PostgreSQL - INNER JOIN execution - Quiz 6medium Set Operations and Advanced Queries - Conditional INSERT with ON CONFLICT - Quiz 2easy Subqueries in PostgreSQL - Why subqueries are needed - Quiz 7medium Subqueries in PostgreSQL - ALL, ANY, SOME with subqueries - Quiz 15hard Subqueries in PostgreSQL - Subqueries in WHERE with IN - Quiz 3easy Views and Materialized Views - Why views matter in PostgreSQL - Quiz 1easy Window Functions in PostgreSQL - Named windows with WINDOW clause - Quiz 13medium