SQL COUNT with CASE WHEN: Conditional Counting Guide (2025)

SQL COUNT with CASE WHEN: Conditional Counting Explained

Written by Sabrina Chao

Sabrina Chao

Sabrina is a technical content writer at Interview Query and has a master's degree in business analytics from Purdue University. Her background includes data analysis, supply chain planning, logistics, and government procurement in both the U.S. and Taiwan. She leverages her international experience to enhance her writing on data and analytics. Outside of work, she enjoys playing tennis or curling up with a good book.

Reviewed by Tiyasa Saha

Tiyasa Saha

Tiyasa is a technical content writer at Interview Query and holds a master’s degree in data science from the University of Massachusetts Dartmouth. She utilizes her expertise in data analysis, machine learning, and data engineering to present complex technical topics in an accessible and engaging way for her readers. Outside of work, Tiyasa enjoys exploring music and experimenting with new dance routines inspired by her formal training.

Published October 7, 2025

Estimated reading time: 25 minutes

Table of contents

Introduction

Basic Syntax of COUNT with CASE WHEN

Real-World Examples of Conditional Counting

Performance & Optimization Tips for COUNT (CASE WHEN)

COUNT (CASE WHEN) Edge Cases & Best Practices

Practice Problems with COUNT CASE WHEN

FAQs (People Also Ask Alignment)

Summary & Key Takeaways

Introduction

When working with SQL, you’ll often need to count rows that meet specific conditions rather than simply tallying all records in a table. This is where combining COUNT with CASE WHEN becomes powerful. Instead of writing multiple queries or filtering data repeatedly, you can calculate conditional counts in a single query. For example, you can track how many orders were shipped, how many were pending, or how many customers fall into different age groups—all within the same result set.

One big advantage is efficiency: using COUNT with CASE WHEN avoids writing separate subqueries or joins for each condition. That means the database engine only scans the data once, saving both memory and computation time. Other methods, like running multiple queries or creating temporary tables often require storing intermediate results, which increases memory usage.

This approach is widely used in reporting, dashboards, and analytics because it makes your queries both more efficient and easier to interpret. By using conditional logic inside aggregate functions, you gain flexibility in slicing data by categories or conditions without creating multiple subqueries.

Basic Syntax of COUNT with CASE WHEN

The COUNT function is one of the most common tools in SQL, and pairing it with a CASE WHEN expression unlocks the ability to count rows conditionally. Instead of returning a simple total, you can apply logic directly inside the aggregate to include only rows that meet specific criteria.

Let’s take a look at how COUNT works with CASE WHEN.

General pattern

The basic structure looks like this:

COUNT(CASE WHEN condition THEN 1 END)

Here’s what happens: if the condition is true, the CASE returns 1, and the COUNT adds it to the count. If the condition is false, the CASE returns NULL, which is ignored by COUNT.

Why NULLs are ignored

The trick is that COUNT only counts non-NULL values. Because the CASE produces NULL when the condition isn’t met, those rows are automatically left out of the count.

Quick example

For example, if you want to compare shipped orders with the total number of orders:

SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders;

This query shows both the total number of orders and how many were shipped—all in one result set.

Real-World Examples of Conditional Counting

Conditional counting with CASE WHEN in SQL is a practical way to break data into meaningful categories without writing multiple queries. Instead of just returning totals, you can apply business rules directly inside your aggregates to count only rows that meet specific conditions. This technique is widely used in scenario based SQL questions across industries—from e-commerce and marketing to HR and customer support to track performance metrics, segment users, and monitor operations in a single, efficient query.

Counting shipped vs unshipped orders (e-commerce)

In an e-commerce orders table, you might want to see how many orders have been shipped vs not shipped.

Table: orders

order_id	status
1	shipped
2	pending
3	shipped
4	canceled
5	shipped

SELECT
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders,
    COUNT(CASE WHEN status != 'shipped' THEN 1 END) AS unshipped_orders
FROM orders;

Output:

shipped_orders	unshipped_orders
3	2

This gives you a quick breakdown of logistics performance—useful for tracking fulfillment rates.

Counting active users by signup channel

If your users table tracks signup_channel (e.g., "web", "mobile", "referral"), you can count active users by channel:

Table: users

user_id	signup_channel	active
1	web	1
2	mobile	0
3	referral	1
4	web	1
5	mobile	1

SELECT
    COUNT(CASE WHEN signup_channel = 'web' AND active = 1 THEN 1 END) AS web_active,
    COUNT(CASE WHEN signup_channel = 'mobile' AND active = 1 THEN 1 END) AS mobile_active,
    COUNT(CASE WHEN signup_channel = 'referral' AND active = 1 THEN 1 END) AS referral_active
FROM users;

Output:

web_active	mobile_active	referral_active
2	1	1

This helps measure marketing ROI by comparing which channels bring the most active users.

Counting employees by department type

Suppose an employees table has department values like "HR", "IT", "Sales". You can summarize headcount by department:

Table: employees

emp_id	department
1	HR
2	Sales
3	IT
4	IT
5	Sales

SELECT
    COUNT(CASE WHEN department = 'HR' THEN 1 END) AS hr_count,
    COUNT(CASE WHEN department = 'IT' THEN 1 END) AS it_count,
    COUNT(CASE WHEN department = 'Sales' THEN 1 END) AS sales_count
FROM employees;

Output:

hr_count	it_count	sales_count
1	2	2

This is a simple but powerful way to create staffing dashboards without complex joins.

Handling multiple conditions (e.g., urgent vs delayed tickets)

In a support system, tickets might have a priority and a status. You can count multiple conditions at once:

Table: tickets

ticket_id	priority	status
1	urgent	open
2	urgent	closed
3	normal	delayed
4	urgent	open
5	normal	open

SELECT
    COUNT(CASE WHEN priority = 'urgent' AND status = 'open' THEN 1 END) AS urgent_open,
    COUNT(CASE WHEN priority = 'urgent' AND status = 'closed' THEN 1 END) AS urgent_closed,
    COUNT(CASE WHEN priority = 'normal' AND status = 'delayed' THEN 1 END) AS normal_delayed
FROM tickets;

This allows teams to monitor SLAs by quickly surfacing how many critical tickets are unresolved or overdue.

Output:

urgent_open	urgent_closed	normal_delayed
2	1	1

COUNT vs SUM with CASE WHEN

COUNT and SUM are the two most common ways to apply conditional aggregation with CASE WHEN, but they behave differently depending on what you need. Understanding when to use each helps you avoid silent errors and write cleaner, more accurate queries.

When to use COUNT

Use COUNT when you want the number of rows meeting a condition.

Pattern:

COUNT(CASE WHEN condition THEN 1 END)
-- counts TRUEs; FALSE -> NULL -> ignored

Also works with non-NULL expressions:

COUNT(CASE WHEN condition THEN some_nonnull_col END)

COUNT(*) counts all rows (no condition).

When to use SUM with CASE

Use SUM when you want a tally or weighted total—it can count like COUNT, but can also sum numbers.

Count as a tally (safe zero with ELSE 0):

SUM(CASE WHEN condition THEN 1 ELSE 0 END)

Conditional totals:

SUM(CASE WHEN condition THEN amount ELSE 0 END)

Numeric vs NULL-handling differences

COUNT(expr) ignores NULL and returns 0 if no rows match.
SUM(expr) ignores NULL, but if all inputs are NULL (i.e., no matches and no ELSE 0), result is NULL.

→ Prefer ELSE 0 (or wrap with COALESCE) to ensure a numeric 0.

Example cases: Side-by-side query and output table

Sample table: orders

order_id	status	amount
1	shipped	120.00
2	pending	80.00
3	shipped	50.00
4	canceled	40.00
5	shipped	60.00

A) Counting shipped orders — `COUNT` vs `SUM`

SELECT
  COUNT(CASE WHEN status = 'shipped' THEN 1 END)                           AS shipped_count_via_count,
  SUM(CASE   WHEN status = 'shipped' THEN 1 ELSE 0 END)                    AS shipped_count_via_sum
FROM orders;

Output

shipped_count_via_count	shipped_count_via_sum
3	3

Both return 3. (SUM uses ELSE 0 to guarantee 0 when none match.)

B) Edge case: no matches — why `ELSE 0` matters

SELECT
  COUNT(CASE WHEN status = 'refunded' THEN 1 END)                          AS refunded_count_via_count,
  SUM(CASE   WHEN status = 'refunded' THEN 1 END)                          AS refunded_count_via_sum_null,
  SUM(CASE   WHEN status = 'refunded' THEN 1 ELSE 0 END)                   AS refunded_count_via_sum_zero
FROM orders;

Output

refunded_count_via_count	refunded_count_via_sum_null	refunded_count_via_sum_zero
0	NULL

COUNT returns 0; SUM without ELSE 0 returns NULL; SUM with ELSE 0 returns 0.

C) Conditional totals (where `SUM` is required)

SELECT
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) AS shipped_revenue,
  SUM(CASE WHEN status <> 'shipped' THEN amount ELSE 0 END) AS unshipped_value
FROM orders;

Output

shipped_revenue	unshipped_value
230.00	120.00

Here SUM computes money totals; COUNT can’t do this.

Rules of thumb

Counting rows? Use COUNT(CASE WHEN … THEN 1 END) or SUM(CASE WHEN … THEN 1 ELSE 0 END); both are fine—just remember the ELSE 0 with SUM.
Adding numbers conditionally? Use SUM(CASE WHEN … THEN value ELSE 0 END).
Want guaranteed numeric outputs (no NULL)? Use ELSE 0 or wrap with COALESCE(…, 0).

COUNTIF vs COUNT CASE WHEN

This section explains how spreadsheet-style COUNTIF functions compare to SQL’s approach for conditional counting. You’ll see why SQL doesn’t have a direct COUNTIF, what alternatives exist, and how different databases handle conditional counts in practice.

COUNTIF in Excel/Google Sheets vs SQL

In Excel or Google Sheets, COUNTIF is a built-in function that counts cells matching a condition:

=COUNTIF(A2:A10, "shipped")

This formula counts all rows in A2:A10 where the value equals "shipped". It’s simple because spreadsheets are optimized for column-based calculations.

In SQL, however, there’s no direct COUNTIF function. Instead, you express the same logic using CASE WHEN inside COUNT or SUM.

Why SQL has no direct COUNTIF function

SQL is designed around set operations, not cell functions.
COUNT only counts non-NULL values, so combining it with a conditional CASE WHEN expression naturally achieves the same as COUNTIF.
This design is more flexible: in SQL you can check multiple conditions, combine with joins, or apply to numeric columns.

Example (e-commerce orders table):

SELECT
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders;

Equivalent to Excel’s:

=COUNTIF(status_range, "shipped")

Best alternatives in MySQL, PostgreSQL, SQL Server

MySQL / PostgreSQL

Both support COUNT(CASE WHEN … THEN 1 END) and SUM(CASE WHEN … THEN 1 ELSE 0 END) as the standard way:

-- Count shipped orders
SELECT
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders;

SQL Server In addition to the CASE method, SQL Server supports the shorthand COUNT(*) FILTER (WHERE …) starting from SQL Server 2017 (and PostgreSQL has long supported it):

-- PostgreSQL or SQL Server (2017+)
SELECT
    COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_orders,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders
FROM orders;

This syntax is closer in spirit to COUNTIF because the filter condition is explicit.

Key takeaway:

Excel/Sheets → COUNTIF is a simple function.
SQL → use COUNT(CASE WHEN …) or SUM(CASE WHEN …) (portable), or COUNT(*) FILTER (WHERE …) (if supported).
All achieve conditional counts—just with different syntaxes.

COUNT (CASE WHEN) Platform-Specific Syntax & Dialect Nuances

This section highlights how different SQL platforms and query languages handle conditional counting. While COUNT(CASE WHEN …) is the standard approach, databases like MySQL, PostgreSQL, SQL Server, Oracle, and even JPQL each offer shorthand functions or dialect-specific alternatives that can make queries more concise or better aligned with their ecosystems.

MySQL – `CASE WHEN` vs `IF()`

In MySQL, you can use both CASE and the simpler IF() function.

-- Using CASE WHEN
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders;

-- Using IF() inside COUNT
SELECT COUNT(IF(status = 'shipped', 1, NULL)) AS shipped_orders
FROM orders;

Both return the same result. IF() is shorter but not portable outside MySQL.

PostgreSQL – `CASE WHEN` vs `FILTER (WHERE …)`

PostgreSQL supports FILTER (WHERE …) for aggregate functions, which makes syntax more readable.

-- Standard CASE WHEN
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders;

-- With FILTER
SELECT COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_orders
FROM orders;

The FILTER version is especially useful when you want multiple conditions side by side.

SQL Server – `CASE WHEN` vs `IIF()`

SQL Server has IIF() as shorthand for simple conditions (since 2012).

-- Using CASE WHEN
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders;

-- Using IIF()
SELECT SUM(IIF(status = 'shipped', 1, 0)) AS shipped_orders
FROM orders;

Note: IIF() always requires both true and false branches, so you typically wrap it in SUM() instead of COUNT().

Oracle – `CASE WHEN` vs `DECODE()`

Oracle supports DECODE(), an older function often used for conditional counting.

-- Using CASE WHEN
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders
FROM orders;

-- Using DECODE
SELECT SUM(DECODE(status, 'shipped', 1, 0)) AS shipped_orders
FROM orders;

DECODE(expr, search, result, default) is less flexible than CASE, but still widely used in legacy Oracle code.

JPQL Example (covers `jpql select case when count(r) > 0`)

JPQL (Java Persistence Query Language) doesn’t support aggregate filters directly, but you can use CASE WHEN with COUNT() inside a SELECT.

SELECT CASE WHEN COUNT(r) > 0
            THEN TRUE
            ELSE FALSE
       END
FROM Reservation r
WHERE r.status = 'CONFIRMED'

This query returns a boolean-like result indicating whether any confirmed reservations exist. It mimics the SQL conditional count pattern in ORM queries.

Advanced Techniques with COUNT CASE WHEN

This section explores how to take conditional counting beyond the basics. You’ll see how to calculate percentages, categorize multiple conditions at once, apply window functions for per-group insights, and combine COUNT CASE WHEN with GROUP BY and HAVING to build richer analytics.

Percentage calculations (e.g., % of paid invoices)

You can use COUNT CASE WHEN with a denominator to calculate percentages.

Table: invoices

invoice_id	status
1	paid
2	unpaid
3	paid
4	overdue
5	paid

Query:

SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END) * 100.0 / COUNT(*) AS pct_paid
FROM invoices;

Output:

pct_paid

60.0

This shows 60% of invoices are paid.

Multi-condition categorizations

Handle multiple categories at once by defining multiple CASE WHEN expressions.

SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END)    AS paid_count,
    COUNT(CASE WHEN status = 'unpaid' THEN 1 END)  AS unpaid_count,
    COUNT(CASE WHEN status = 'overdue' THEN 1 END) AS overdue_count
FROM invoices;

Output:

paid_count	unpaid_count	overdue_count
3	1	1

This mimics pivot-table style summaries directly in SQL.

Window functions with CASE WHEN

Combine CASE WHEN with window functions to add conditional counts across partitions (e.g., per customer).

Table: orders

order_id	customer_id	status
1	101	shipped
2	101	pending
3	102	shipped
4	102	shipped
5	103	canceled

Query:

SELECT
    order_id,
    customer_id,
    status,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END)
        OVER (PARTITION BY customer_id) AS shipped_per_customer
FROM orders;

Output:

order_id	customer_id	status	shipped_per_customer
1	101	shipped	1
2	101	pending	1
3	102	shipped	2
4	102	shipped	2
5	103	canceled	0

This helps identify how many shipped orders each customer has without collapsing rows.

Combining with GROUP BY and HAVING

Conditional counting pairs naturally with GROUP BY and HAVING for analytics.

Table: tickets

ticket_id	agent	status
1	Alice	open
2	Alice	closed
3	Bob	open
4	Bob	open
5	Bob	closed

Query:

SELECT
    agent,
    COUNT(CASE WHEN status = 'open' THEN 1 END) AS open_tickets,
    COUNT(CASE WHEN status = 'closed' THEN 1 END) AS closed_tickets
FROM tickets
GROUP BY agent
HAVING COUNT(CASE WHEN status = 'open' THEN 1 END) > 1;

Output:

agent	open_tickets	closed_tickets
Bob	2	1

This finds agents with more than one open ticket.

Performance & Optimization Tips for COUNT (CASE WHEN)

Efficient use of COUNT(CASE WHEN …) is beyond basic syntax, it is about performance, readability, and scalability. This section explores optimization strategies, from choosing between COUNT vs SUM, leveraging indexes, and simplifying queries with CTEs or materialized views, to taking advantage of dialect-specific features like PostgreSQL’s FILTER. These practices help keep conditional counts accurate, fast, and maintainable even on large datasets.

COUNT vs SUM efficiency

Both COUNT(CASE WHEN …) and SUM(CASE WHEN …) achieve the same result for conditional counts. But they differ based on the following aspects.

Aspect	`COUNT(CASE WHEN …)`	`SUM(CASE WHEN …)`
NULL handling	Skips `NULL` by default	Needs `ELSE 0` (or `COALESCE`) for predictable results
Readability	Cleaner for pure counts	Clearer when mixing counts + numeric totals
Performance	Slightly faster in some cases; both usually optimized equivalently	Equivalent performance in most databases
Best use case	Simple conditional counts	Counts + conditional numeric sums together

Rule of thumb: pick the form that makes intent clearer to future readers of your SQL.

Indexing strategies

Indexes can drastically improve conditional counts, especially when filtering frequently.

Single-column indexes: If you often count status = 'shipped', an index on status will speed up lookups.
Composite indexes: For conditions like status = 'shipped' AND customer_id = 101, use a composite index (status, customer_id).

Partial indexes (PostgreSQL):

CREATE INDEX idx_orders_shipped ON orders(order_id) WHERE status = 'shipped';

This optimizes queries counting only shipped rows. Indexes help reads but add overhead to writes—balance based on workload.

CTEs & materialized views for complex counts

When your query involves multiple conditional counts, Common Table Expressions (CTEs) or materialized views can reduce duplication.

WITH base AS (
SELECT customer_id, status
FROM orders
)
SELECT
customer_id,
COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count
FROM base
GROUP BY customer_id;

CTEs make queries easier to read and maintain.
Materialized views are pre-computed, making repeated conditional counts much faster in reporting dashboards.

PostgreSQL `FILTER` vs CASE WHEN performance

PostgreSQL offers both CASE WHEN and FILTER (WHERE …).

-- CASE WHEN
COUNT(CASE WHEN status = 'shipped' THEN 1 END)

-- FILTER
COUNT(*) FILTER (WHERE status = 'shipped')

Readability: FILTER is shorter and easier to scan when you need many conditions.
Performance: Both compile into similar query plans. But FILTER can be slightly more efficient because the condition applies directly during aggregation, skipping unnecessary expression evaluation.

For Postgres, prefer FILTER in reporting queries. For cross-platform SQL, stick with CASE WHEN.

COUNT (CASE WHEN) Edge Cases & Best Practices

Conditional counts are powerful, but subtle mistakes can lead to misleading results. These best practices help ensure accuracy and clarity.

Handling NULL values explicitly

Handling NULL values is an essential aspect of most SQL queries. By default, COUNT(expr) ignores NULL. This works in your favor most of the time, but can also hide missing data.

Example – counting active users

SELECT
    COUNT(CASE WHEN active = 1 THEN 1 END) AS active_users,
    COUNT(CASE WHEN active = 0 THEN 1 END) AS inactive_users
FROM users;

If active is NULL for some users, those rows are skipped entirely. If you want to treat NULL explicitly:

SELECT
    COUNT(CASE WHEN active = 1 THEN 1 END) AS active_users,
    COUNT(CASE WHEN active = 0 THEN 1 END) AS inactive_users,
    COUNT(CASE WHEN active IS NULL THEN 1 END) AS unknown_status
FROM users;

Tip: Always decide whether NULL should mean “unknown” (count separately) or “inactive” (fold into another category).

Avoiding overlapping conditions

When writing multiple CASE WHEN conditions, make sure categories don’t unintentionally overlap.

Bad (overlap):

SELECT
    COUNT(CASE WHEN amount > 100 THEN 1 END) AS high_value,
    COUNT(CASE WHEN amount > 50 THEN 1 END)  AS mid_value
FROM orders;

Here, a row with amount = 120 is counted in both categories.

Good (mutually exclusive):

SELECT
    COUNT(CASE WHEN amount > 100 THEN 1 END) AS high_value,
    COUNT(CASE WHEN amount BETWEEN 51 AND 100 THEN 1 END) AS mid_value
FROM orders;

Tip: Write conditions so each row fits into exactly one bucket, unless intentional.

Time-based conditional counts (daily/weekly/monthly)

A common pattern is to track events by time intervals.

Table: logins

user_id	login_date
1	2025-09-01
2	2025-09-01
3	2025-09-02
1	2025-09-03

Query – daily active users:

SELECT
    login_date,
    COUNT(DISTINCT user_id) AS dau
FROM logins
GROUP BY login_date
ORDER BY login_date;

Output:

login_date	dau
2025-09-01	2
2025-09-02	1
2025-09-03	1

Query – conditional monthly counts (active vs inactive):

SELECT
    DATE_TRUNC('month', login_date) AS month,
    COUNT(DISTINCT CASE WHEN active = 1 THEN user_id END) AS active_users,
    COUNT(DISTINCT CASE WHEN active = 0 THEN user_id END) AS inactive_users
FROM users_log
GROUP BY DATE_TRUNC('month', login_date)
ORDER BY month;

Time-based aggregations are where CASE WHEN + GROUP BY really shine in analytics dashboards.

Practice Problems with COUNT CASE WHEN

The best way to master conditional counting is by solving hands-on and case study type problems. This section walks through real-world scenarios like tracking employees by type, shipments by delivery status, or users by verification flag, where COUNT(CASE WHEN …) turns business rules into clear, actionable SQL queries. Each exercise includes prompts, hints, and expected outputs to help you practice interview-style thinking.

Count full-time vs part-time employees per department

Table: employees

emp_id	dept	employment_type
1	HR	full-time
2	HR	part-time
3	IT	full-time
4	IT	full-time
5	Sales	part-time

Prompt: Write a SQL query to count the number of full-time and part-time employees in each department.

Hint:

We need two separate conditions: full-time and part-time.
Your first instinct should be CASE WHEN because you’re applying different conditions inside the same aggregation.
Group results by dept to split counts per department.

Query

SELECT
  dept,
  COUNT(CASE WHEN employment_type = 'full-time' THEN 1 END) AS full_time_count,
  COUNT(CASE WHEN employment_type = 'part-time' THEN 1 END) AS part_time_count
FROM employees
GROUP BY dept
ORDER BY dept;

Expected Output

dept	full_time_count	part_time_count
HR	1	1
IT	2	0
Sales	0	1

Using CASE WHEN inside COUNT lets you calculate multiple conditional aggregates in a single scan, instead of running separate queries.

Count Delayed vs On-time Shipments per Month

Table: shipments

id	expected_date	delivered_date
1	2025-09-01	2025-09-02
2	2025-09-05	2025-09-04
3	2025-09-15	2025-09-20
4	2025-10-01	2025-10-01
5	2025-10-05	2025-10-07

Prompt: For each month (by expected_date), count delayed (delivered_date > expected_date) vs on-time shipments.

Hint:

Derive a month key from expected_date.
Two conditions (delayed/on-time) → CASE WHEN buckets; GROUP BY the month.

Query:

SELECT
  DATE_TRUNC('month', expected_date) AS month,
  COUNT(CASE WHEN delivered_date >  expected_date THEN 1 END) AS delayed,
  COUNT(CASE WHEN delivered_date <= expected_date THEN 1 END) AS on_time
FROM shipments
GROUP BY DATE_TRUNC('month', expected_date)
ORDER BY month;

Expected Output

month	delayed	on_time
2025-09-01	2	1
2025-10-01	1	1

CASE WHEN lets you pivot conditions into columns while grouping by time in one concise query.

Count Verified vs Non-Verified Users

Table: users

user_id	verified
1	1
2	0
3	NULL
4	1
5	0

Prompt: Count verified and non-verified users (treat NULL as non-verified).

Hint:

Define your business rule for NULL up front.
Two buckets (verified vs not) → CASE WHEN; no GROUP BY needed unless you want per-segment splits.

Query

SELECT
  COUNT(CASE WHEN verified = 1 THEN 1 END) AS verified_users,
  COUNT(CASE WHEN verified = 0 OR verified IS NULL THEN 1 END) AS non_verified_users
FROM users;

Expected Output

verified_users	non_verified_users
2	3

You get explicit control of NULLs and clear category totals in a single statement.

Count Customers with Purchases > $1000

Table: orders

order_id	customer_id	amount
1	101	500
2	101	600
3	102	300
4	103	1200
5	104	200

Prompt: Count how many distinct customers have total purchases > 1000.

Hint:

First, aggregate spend per customer; then filter with HAVING SUM(amount) > 1000.
Finally, count those customers (outer query).

Query:

SELECT COUNT(*) AS customers_over_1000
FROM (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 1000
) t;

Expected Output

customers_over_1000

Combining GROUP BY + HAVING yields a set of qualified customers you can count, join, or analyze further.

Count Support Tickets by Urgency Level

Table: tickets

id	priority
1	urgent
2	high
3	urgent
4	normal
5	low

Prompt: Count tickets for each priority level (urgent/high/normal/low).

Hint:

Parallel category buckets → one CASE WHEN per level.
If you later need per-agent splits, add agent and GROUP BY agent.

Query

SELECT
  COUNT(CASE WHEN priority = 'urgent' THEN 1 END) AS urgent_count,
  COUNT(CASE WHEN priority = 'high'   THEN 1 END) AS high_count,
  COUNT(CASE WHEN priority = 'normal' THEN 1 END) AS normal_count,
  COUNT(CASE WHEN priority = 'low'    THEN 1 END) AS low_count
FROM tickets;

Expected Output

urgent_count	high_count	normal_count	low_count
2	1	1	1

CASE WHEN acts like a compact pivot, turning label-based conditions into side-by-side KPIs.

Now let’s practice more real SQL interview questions!

FAQs (People Also Ask Alignment)

Can SQL COUNT only specific rows?

Yes. Use conditional aggregation—COUNT over a CASE that returns a non-NULL only when the condition is true.

-- Shipped orders only
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped
FROM orders;

How is COUNT with CASE WHEN different from COUNTIF?

Excel/Sheets have COUNTIF(range, condition).

SQL has no COUNTIF; you replicate it with:

-- Equivalent of COUNTIF(status = 'shipped')
SELECT COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped
FROM orders;

Same idea, just expressed with CASE WHEN inside an aggregate.

Does COUNT with CASE WHEN work in MySQL/PostgreSQL/Oracle?

Yes, COUNT with CASE WHEN is portable everywhere.

MySQL: also allows COUNT(IF(condition,1,NULL)) or SUM(condition) (TRUE=1/FALSE=0).
PostgreSQL: supports the above and the concise COUNT(*) FILTER (WHERE condition).
Oracle: use CASE (or legacy DECODE) with COUNT/SUM. (No FILTER clause.)

Examples:

-- MySQL
SELECT COUNT(IF(status='shipped',1,NULL)) AS shipped FROM orders;

-- PostgreSQL
SELECT COUNT(*) FILTER (WHERE status='shipped') AS shipped FROM orders;

-- Oracle (and portable)
SELECT COUNT(CASE WHEN status='shipped' THEN 1 END) AS shipped FROM orders;

Is COUNT CASE WHEN faster than SUM CASE WHEN?

In practice, they’re usually the same (optimizers produce similar plans). Pick for clarity:

-- Both count matches
COUNT(CASE WHEN cond THEN 1 END)
SUM(CASE WHEN cond THEN 1 ELSE 0 END)

If you use SUM, include ELSE 0 (or COALESCE) to avoid NULL results when no rows match.

Can you count multiple conditions with CASE WHEN?

Yes, multiple conditions with CASE WHEN can be counted using side-by-side columns or grouped buckets.

-- Parallel KPIs in one pass
SELECT
  COUNT(CASE WHEN status='shipped' THEN 1 END) AS shipped,
  COUNT(CASE WHEN status='pending' THEN 1 END) AS pending,
  COUNT(CASE WHEN status='canceled' THEN 1 END) AS canceled
FROM orders;

-- Or bucket + GROUP BY
SELECT
  CASE
    WHEN amount > 1000 THEN 'high'
    WHEN amount BETWEEN 501 AND 1000 THEN 'mid'
    ELSE 'low'
  END AS bucket,
  COUNT(*) AS cnt
FROM orders
GROUP BY 1
ORDER BY 1;

Summary & Key Takeaways

COUNT with CASE WHEN is one of the most versatile SQL patterns for conditional counting, letting you calculate multiple KPIs in a single pass without extra queries. It works across all major databases, with dialect-specific shortcuts like PostgreSQL’s FILTER, MySQL’s IF(), SQL Server’s IIF(), and Oracle’s DECODE().

For counting, you can use either COUNT(CASE WHEN) or SUM with CASE WHEN (Conditional Totals), with ELSE 0 ensuring no unexpected NULL results. This approach scales easily to multiple categories, pairs well with GROUP BY, HAVING, and window functions, and is essential for dashboards, reporting, and interview-style problems.

Just remember to handle NULL values explicitly, write non-overlapping conditions, index frequently filtered columns, and use partial or composite indexes when appropriate. Done right, COUNT CASE WHEN delivers fast, clean, and accurate insights from complex datasets.

Practice Next

Ready to nail your next SQL interview? Start with our SQL Question Bank → to practice real-world scenario questions used in top interviews, questions. Sign up for Interview Query to test yourself with Mock Interviews → today.

Looking for hands-on problem-solving?

Test your skills with real-world challenges → from top companies to cement concepts before the interview.

Want role-specific SQL prep?

Browse tailored hubs like SQL for Data Analysts → and Data Scientist SQL Questions →. Pair with our AI Interview Tool → to sharpen your storytelling.

Need 1:1 guidance on your interview strategy?

Explore Interview Query’s Coaching Program → to work with a mentor, refine your prep, and build confidence.

IBM Now Wants their Consultants to Code. What’s Happening?Nvidia CEO Reveals the Job That’ll Win the AI Race Interview Query vs Data Interview: Which Platform Is Best for Data Science Prep?AI Might Write Your Code, But It Still Needs a Babysitter Weighted Average in SQL: Examples, Formulas, and Best Practices (2025 Guide)

SQL COUNT with CASE WHEN: Conditional Counting Explained

Introduction

Basic Syntax of COUNT with CASE WHEN

General pattern

Why NULLs are ignored

Quick example

Real-World Examples of Conditional Counting

Counting shipped vs unshipped orders (e-commerce)

Counting active users by signup channel

Counting employees by department type

Handling multiple conditions (e.g., urgent vs delayed tickets)

COUNT vs SUM with CASE WHEN

When to use COUNT

When to use SUM with CASE

Numeric vs NULL-handling differences

Example cases: Side-by-side query and output table

A) Counting shipped orders — COUNT vs SUM

B) Edge case: no matches — why ELSE 0 matters

C) Conditional totals (where SUM is required)

COUNTIF vs COUNT CASE WHEN

COUNTIF in Excel/Google Sheets vs SQL

Why SQL has no direct COUNTIF function

Best alternatives in MySQL, PostgreSQL, SQL Server

COUNT (CASE WHEN) Platform-Specific Syntax & Dialect Nuances

MySQL – CASE WHEN vs IF()

PostgreSQL – CASE WHEN vs FILTER (WHERE …)

SQL Server – CASE WHEN vs IIF()

Oracle – CASE WHEN vs DECODE()

JPQL Example (covers jpql select case when count(r) > 0)

Advanced Techniques with COUNT CASE WHEN

Percentage calculations (e.g., % of paid invoices)

Multi-condition categorizations

Window functions with CASE WHEN

Combining with GROUP BY and HAVING

Performance & Optimization Tips for COUNT (CASE WHEN)

COUNT vs SUM efficiency

Indexing strategies

CTEs & materialized views for complex counts

PostgreSQL FILTER vs CASE WHEN performance

COUNT (CASE WHEN) Edge Cases & Best Practices

Handling NULL values explicitly

Avoiding overlapping conditions

Time-based conditional counts (daily/weekly/monthly)

Practice Problems with COUNT CASE WHEN

Count full-time vs part-time employees per department

Count Delayed vs On-time Shipments per Month

Count Verified vs Non-Verified Users

Count Customers with Purchases > $1000

Count Support Tickets by Urgency Level

FAQs (People Also Ask Alignment)

Can SQL COUNT only specific rows?

How is COUNT with CASE WHEN different from COUNTIF?

Does COUNT with CASE WHEN work in MySQL/PostgreSQL/Oracle?

Is COUNT CASE WHEN faster than SUM CASE WHEN?

Can you count multiple conditions with CASE WHEN?

Summary & Key Takeaways

Practice Next

A) Counting shipped orders — `COUNT` vs `SUM`

B) Edge case: no matches — why `ELSE 0` matters

C) Conditional totals (where `SUM` is required)

MySQL – `CASE WHEN` vs `IF()`

PostgreSQL – `CASE WHEN` vs `FILTER (WHERE …)`

SQL Server – `CASE WHEN` vs `IIF()`

Oracle – `CASE WHEN` vs `DECODE()`

JPQL Example (covers `jpql select case when count(r) > 0`)

PostgreSQL `FILTER` vs CASE WHEN performance