SQL SUM CASE WHEN: Complete Guide to Conditional Aggregation

In analytics SQL, the fastest way to slice metrics without multiple passes over your table is the SUM(CASE WHEN …) pattern which is often called a SQL conditional sum. It lets you total only the rows that meet a condition (or set of conditions) directly inside a single grouped query. If you’ve searched for SUM (CASE WHEN) questions in SQL, this is exactly what you’re looking for.

Here’s a quick example: daily revenue split by payment status—one scan, multiple metrics.

SELECT
  DATE(order_ts) AS order_date,
  SUM(CASE WHEN status = 'paid'   THEN amount ELSE 0 END) AS paid_revenue,
  SUM(CASE WHEN status = 'refund' THEN amount ELSE 0 END) AS refunded_amount,
  SUM(CASE WHEN status = 'paid'   THEN 1      ELSE 0 END) AS paid_orders
FROM orders
GROUP BY 1
ORDER BY 1;

This single result replaces several separate queries and avoids extra joins or temp tables—classic SQL sum case when done right.

At a glance, SUM() adds numbers, while CASE WHEN returns a value conditionally. Combine them and you get “sum this value only when the predicate is true.” That’s the entire idea behind SUM(CASE WHEN) in SQL (also seen as SQL sum if condition or case sum).

Simple syntax template:

SUM(
  CASE
    WHEN <condition> THEN <numeric_value>
    ELSE 0
  END
) AS metric_name

Beginner-friendly example: count 30-day active users and their total purchases in one query.

SELECT
  country,
  SUM(CASE WHEN last_login >= CURRENT_DATE - INTERVAL '30 days'
           THEN 1 ELSE 0 END)                           AS active_30d_users,
  SUM(CASE WHEN last_login >= CURRENT_DATE - INTERVAL '30 days'
           THEN purchases ELSE 0 END)                   AS active_30d_purchases
FROM users
GROUP BY country;

• When the condition is true, we add the value (often 1 for counts).
• When false, we add 0, leaving the sum unchanged.
• You can stack many such expressions to build a compact KPI table.

Conditional aggregation lets you compute multiple KPIs in a single grouped query, instead of running and maintaining separate queries per metric. By pushing logic into measures with a SQL sum with condition, you keep filters consistent, reduce joins and temp tables, and improve performance with one pass over the data. It also scales nicely to complex segment logic (e.g., SQL sum if multiple conditions) while staying readable and testable for analytics and interview use cases.

Common scenarios

• Active users: count or total activity within time windows without extra subqueries.
• Failed transactions: tally errors alongside successes for reliable monitoring.
• Pivot-style aggregations: produce per-channel or per-segment columns without a PIVOT step.

This section shows how to turn SUM(CASE WHEN …) into practical, interview-ready patterns. You’ll see compact queries that count outcomes by segment, blend conditional sums with window functions for running KPIs, and produce pivot-style reports—all in a single grouped pass. Use these templates to speed up dashboards, A/B metrics, and event pipelines without scattering logic across multiple queries.

Counting Event Types Per User

When you need SUM(CASE WHEN multiple conditions SQL) in one pass—say, counting successes and failures by device, you can stack conditional measures in a single SQLSUM CASE WHEN multiple conditions GROUP BY query.

-- events(user_id, event_type, device, success, amount)
SELECT
  user_id,
  SUM(CASE WHEN event_type = 'login'  AND success = 1 THEN 1 ELSE 0 END) AS logins_ok,
  SUM(CASE WHEN event_type = 'login'  AND success = 0 THEN 1 ELSE 0 END) AS logins_fail,
  SUM(CASE WHEN event_type = 'purchase' AND device = 'iOS'     THEN amount ELSE 0 END) AS ios_purchase_amt,
  SUM(CASE WHEN event_type = 'purchase' AND device = 'Android' THEN amount ELSE 0 END) AS android_purchase_amt
FROM events
GROUP BY user_id;

Combining SUM, CASE, and MAX

For advanced SQL CASE WHEN SUM GROUP BY case patterns, mix conditional sums with window functions to track running KPIs and milestone dates—classic SQL conditional aggregation with windows.

-- Running purchase amount and most recent purchase timestamp per user
SELECT
  user_id,
  event_ts,
  SUM(CASE WHEN event_type = 'purchase' THEN amount ELSE 0 END)
    OVER (PARTITION BY user_id ORDER BY event_ts
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_purchase_amt,
  MAX(CASE WHEN event_type = 'purchase' THEN event_ts END)
    OVER (PARTITION BY user_id ORDER BY event_ts
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_purchase_ts
FROM events
ORDER BY user_id, event_ts;

Pivot-Style Aggregation

Build compact, pivot-like reports by projecting segments into columns with multiple conditional sums—handy for SQL SUM multiple columns needs (and identical in spirit to SUM CASE WHENMySQL).

-- campaign performance split by channel and outcome
-- sessions(campaign_id, channel, converted, revenue)
SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'paid'    THEN 1 ELSE 0 END) AS sessions_paid,
  SUM(CASE WHEN channel = 'organic' THEN 1 ELSE 0 END) AS sessions_organic,
  SUM(CASE WHEN converted = 1       THEN 1 ELSE 0 END) AS conversions_total,
  SUM(CASE WHEN channel = 'paid'    THEN revenue ELSE 0 END) AS revenue_paid,
  SUM(CASE WHEN channel = 'organic' THEN revenue ELSE 0 END) AS revenue_organic
FROM sessions
GROUP BY campaign_id;

Different SQL engines support conditional sums the same way conceptually, but each offers its own conveniences and idioms. Below is a quick tour of the most common patterns—classic SUM(CASE WHEN … THEN … ELSE 0 END), plus dialect-specific options like PostgreSQL’s FILTER, SQL Server’s IIF/PIVOT, and Oracle’s DECODE so you can write clear, efficient queries in whichever platform you use.

MySQL

The pattern is straightforward: use SUM(CASE WHEN … THEN … ELSE 0 END) in grouped queries. This is the canonical MySQL ****SUM(CASE WHEN) approach and works across versions (including 5.7).

SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'paid'    THEN revenue ELSE 0 END) AS rev_paid,
  SUM(CASE WHEN channel = 'organic' THEN revenue ELSE 0 END) AS rev_org
FROM sessions
GROUP BY campaign_id;

PostgreSQL

You can write the same PostgreSQL SUM(CASE WHEN) expression—or use the elegant FILTER (WHERE …) syntax, which often reads cleaner and avoids ELSE 0.

SELECT
  campaign_id,
  SUM(revenue) FILTER (WHERE channel = 'paid')    AS rev_paid,
  SUM(revenue) FILTER (WHERE channel = 'organic') AS rev_org
FROM sessions
GROUP BY campaign_id;

SQL Server

Standard SQL server SUM(CASE WHEN) works everywhere. For simple binary conditions, IIF() is a concise alternative; for wide cross-tabs, the PIVOT operator can replace multiple CASEs (with the trade-off that columns must be enumerated).

-- CASE WHEN
SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'paid' THEN revenue ELSE 0 END) AS rev_paid
FROM dbo.sessions
GROUP BY campaign_id;

-- IIF() variant
SELECT
  campaign_id,
  SUM(IIF(channel = 'paid', revenue, 0)) AS rev_paid
FROM dbo.sessions
GROUP BY campaign_id;

Oracle

Modern Oracle supports CASE WHEN identically; older codebases may use DECODE() for equality-only branches. Use Oracle SUM(CASE WHEN) for complex predicates, and DECODE for simple mappings.

-- CASE WHEN (flexible conditions)
SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'paid' THEN revenue ELSE 0 END) AS rev_paid
FROM sessions
GROUP BY campaign_id;

-- DECODE (equality-only)
SELECT
  campaign_id,
  SUM(DECODE(channel, 'paid', revenue, 0)) AS rev_paid
FROM sessions
GROUP BY campaign_id;

Conditional aggregation can be both accurate and fast if you design it intentionally. Before adding SUM(CASE WHEN …) to every query, align on the business grain, guard against silent NULL pitfalls, and give the optimizer what it needs (indexes, clean predicates). The table below captures practical habits that keep conditional sums predictable, maintainable, and performant across SQL dialects.

Quick checklist

• Include ELSE 0 in every conditional measure.
• Pre-aggregate to the correct grain before applying conditional sums.
• Index GROUP BY and predicate columns.
• Use CTEs and clear aliases to make intent obvious.
• Consider FILTER (Postgres) or PIVOT (SQL Server/Oracle) when it improves clarity.

Follow these practices and your SUM with condition SQL patterns will stay fast, auditable, and easy to extend.

Conditional sums like SUM(CASE WHEN … THEN value ELSE 0 END) are powerful but easy to get subtly wrong. Small mistakes with NULLs, mixed data types, or over-nested logic can skew metrics and slow queries without obvious errors. Below are the most common pitfalls and simple patterns to keep your conditional aggregations accurate, readable, and fast.

Handling NULL values

SUM() skips NULL, so a CASE without ELSE can silently undercount. In any SQL sum based on condition, make the false branch explicit and null-safe. Also normalize the input measure so missing amounts don’t leak through.

-- Risky: NULLs propagate; false branch is NULL
SUM(CASE WHEN status = 'paid' THEN amount END)

-- Safer: explicit else + null-safe amount
SUM(CASE WHEN status = 'paid' THEN COALESCE(amount, 0) ELSE 0 END)

Mixing data types in CASE

Every branch of CASE should return the same numeric type. Mixing integers, strings ('0'), and decimals forces implicit casts that can slow queries and cause rounding surprises. If needed, cast deliberately so your SQL addition in SQL query stays precise and predictable.

-- Don’t do this: branches return different types
SUM(CASE WHEN is_refund = 1 THEN -amount ELSE '0' END)

-- Do this: consistent numeric type
SUM(CASE WHEN is_refund = 1 THEN -CAST(amount AS DECIMAL(12,2)) ELSE 0 END)

Nested CASE WHEN readability issues

Deeply nested logic is hard to debug and easy to mis-order. Break complex predicates into small flags in a CTE, then sum those flags. This keeps conditional aggregation clear and maintainable.

WITH base AS (
  SELECT
    user_id,
    amount,
    status,
    channel,
    -- simple, testable flags
    (CASE WHEN status = 'paid'   THEN 1 ELSE 0 END) AS is_paid,
    (CASE WHEN status = 'refund' THEN 1 ELSE 0 END) AS is_refund,
    (CASE WHEN channel = 'ads'   THEN 1 ELSE 0 END) AS is_ads
  FROM orders
)
SELECT
  user_id,
  SUM(CASE WHEN is_paid = 1   THEN amount ELSE 0 END) AS paid_rev,
  SUM(CASE WHEN is_refund = 1 THEN amount ELSE 0 END) AS refunded_rev,
  SUM(CASE WHEN is_ads = 1    THEN amount ELSE 0 END) AS ads_rev
FROM base
GROUP BY user_id;

Quick guidance

Keep ELSE 0 in every conditional measure; coalesce inputs to 0; standardize types across CASE branches; and factor complex logic into CTEs or computed flags. These habits prevent silent errors and keep conditional sums fast, readable, and interview-ready.

Explore how SUM(CASE WHEN …) shows up in real interview prompts—from retail categorization to HR rollups and product funnel rates. Each example demonstrates a compact, single-pass pattern you can adapt to your own schemas.

1 . Categorize and sum sales by region based on amount thresholds

This is a classic SQL ****SUM CASE example. The approach is to use CASE WHEN to bucket sales into categories (e.g., small, medium, large) and then sum by region. Group the result by region to show totals across each threshold. It’s a practical SQL SUM IF condition pattern that mirrors real reporting needs in retail and e-commerce.

SELECT 
    region,
    SUM(sale_amount) AS total_sales,
    SUM(CASE 
            WHEN sale_date BETWEEN '2023-07-01' AND '2023-07-31' THEN sale_amount
            ELSE 0 
        END) AS promotional_sales,
    SUM(CASE 
            WHEN (sale_amount >= 2000 OR region = 'East') AND NOT (sale_date BETWEEN '2023-07-01' AND '2023-07-31') THEN sale_amount
            ELSE 0 
        END) AS premium_sales,
    SUM(CASE 
            WHEN sale_amount < 2000 AND region <> 'East' AND NOT (sale_date BETWEEN '2023-07-01' AND '2023-07-31') THEN sale_amount
            ELSE 0 
        END) AS standard_sales
FROM 
    sales
GROUP BY 
    region;

2 . Write a SQL query to calculate and report HR salary totals by department

Here, group employees by department and apply conditional sums for salary bands or roles. You might use SUM(CASE WHEN role = 'Manager' THEN salary ELSE 0 END) to break down salaries by job type. This highlights applied use of a SQL ****SUM CASE in HR analytics scenarios.

SELECT
  job_title,
  SUM(salary) AS total_salaries,
  SUM(overtime_hours * overtime_rate) AS total_overtime_payments,
  SUM(salary + (overtime_hours * overtime_rate)) AS total_compensation
FROM
  employees
GROUP BY
  job_title;

3 . Write a SQL query to calculate the daily number of successful posts

Use SUM(CASE WHEN … THEN 1 ELSE 0 END) to count conditional events per day. The numerator is posts submitted, the denominator is posts started (post_enter). Dividing them gives the daily success rate. Filtering to January 2020 ensures only that month is included. This pattern is common in SQL interviews because it tests conditional aggregation (CASE inside SUM), correct filtering by date range, and ratio computation.

SELECT
  DATE(created_at) AS dt,
  SUM(CASE WHEN action = 'post_submit' THEN 1 ELSE 0 END) * 1.0
    / SUM(CASE WHEN action = 'post_enter' THEN 1 ELSE 0 END) AS post_success_rate
FROM events
WHERE created_at >= '2020-01-01'
  AND created_at < '2020-02-01'
GROUP BY DATE(created_at)
ORDER BY dt;

Sharpen your conditional aggregation skills with targeted, real-world prompts spanning finance, operations, and telecom. Each question reinforces SUM(CASE WHEN …) patterns such as filtering, bucketing, and ratio math, so you build speed and accuracy for interviews.

1 . Write a SQL query to count customers who signed up in January 2020 and calculate their total payments

Use conditional aggregation to total only the rows that match your logic. Here, two SUM(CASE WHEN …) terms capture sent and received amounts separately, filtered to payment_state = 'success' and the first 30 days after signup. Group by user to get a per-user 30-day total (in cents), then count users whose total exceeds 100 * 100. This pattern cleanly avoids double counting and keeps all logic inside the aggregates. This demonstrates a SQL group by sum with condition pattern often used in finance and subscription businesses.

WITH jan_users AS (
  SELECT
    id AS user_id,
    created_at AS signup_at
  FROM users
  WHERE created_at >= '2020-01-01'
    AND created_at <  '2020-02-01'
),
user_30d_volume AS (
  SELECT
    u.user_id,
    -- successful outgoing + incoming within 30 days of signup
    SUM(CASE
          WHEN p.payment_state = 'success'
           AND p.created_at >= u.signup_at
           AND p.created_at <  DATE_ADD(u.signup_at, INTERVAL 30 DAY)
           AND p.sender_id = u.user_id
          THEN p.amount_cents ELSE 0
        END)
    +
    SUM(CASE
          WHEN p.payment_state = 'success'
           AND p.created_at >= u.signup_at
           AND p.created_at <  DATE_ADD(u.signup_at, INTERVAL 30 DAY)
           AND p.recipient_id = u.user_id
          THEN p.amount_cents ELSE 0
        END) AS cents_30d
  FROM jan_users u
  LEFT JOIN payments p
    ON (p.sender_id = u.user_id OR p.recipient_id = u.user_id)
  GROUP BY u.user_id
)
SELECT COUNT(*) AS num_customers
FROM user_30d_volume
WHERE cents_30d > 10000;   -- > $100 in cents

2 . Write a SQL query to calculate and return each department’s monthly expenses

Use SUM(CASE WHEN … THEN amount ELSE 0 END) to conditionally total only 2022 expenses per department while still including all departments. Wrap this in a CTE and apply AVG(total_expense) OVER () to calculate the cross-department average. Finally, round the average to two decimals and order by total_expense DESC to show the highest-spending departments first. This pattern highlights how conditional aggregation can replace a WHERE filter when you want to keep all groups visible. This combines grouping with conditional logic for detailed reporting. It’s a strong example of SUMIF SQL in a corporate finance context.

WITH dept_totals AS (
  SELECT
    d.name AS department_name,
    SUM(CASE 
          WHEN e.date >= '2022-01-01' AND e.date < '2023-01-01' 
          THEN e.amount ELSE 0 
        END) AS total_expense
  FROM departments d
  LEFT JOIN expenses e ON d.id = e.department_id
  GROUP BY d.name
)
SELECT
  department_name,
  total_expense,
  ROUND(AVG(total_expense) OVER (), 2) AS average_expense
FROM dept_totals
ORDER BY total_expense DESC;

3 . Calculate the number of SMS confirmations grouped by country

Use conditional aggregation to count only confirmations tied to confirmation SMSs sent on Feb 28, 2020. First, for each (phone_number, response_date), pick the latest confirmation SMS sent before the response (MAX(ds)), ensuring the user confirms the most recent message. Join back to fetch its carrier/country, de-duplicate per phone/SMS, then SUM(CASE WHEN sms_ds is on 2020-02-28 THEN 1 END) per (carrier, country). This keeps all logic in aggregates while honoring the “confirm only the latest message” rule. This illustrates SQL SUMIF with group by in a communications or telecom setting.

WITH latest_confirm_sms AS (         -- latest confirmation SMS before each response
  SELECT
    c.phone_number,
    c.date AS response_date,
    MAX(s.ds) AS sms_ds
  FROM confirmers c
  JOIN sms_sends s
    ON s.phone_number = c.phone_number
   AND s.type = 'confirmation'
   AND s.ds < c.date + INTERVAL 1 DAY   -- SMS must be before (or on) response day
  GROUP BY c.phone_number, c.date
),
confirmed_msgs AS (                  -- attach carrier/country for that SMS
  SELECT DISTINCT
    s.carrier,
    s.country,
    l.phone_number,
    l.sms_ds
  FROM latest_confirm_sms l
  JOIN sms_sends s
    ON s.phone_number = l.phone_number
   AND s.type = 'confirmation'
   AND s.ds = l.sms_ds
)
SELECT
  carrier,
  country,
  SUM(CASE
        WHEN sms_ds >= '2020-02-28' AND sms_ds < '2020-02-29' THEN 1
        ELSE 0
      END) AS unique_numbers
FROM confirmed_msgs
GROUP BY carrier, country
ORDER BY unique_numbers DESC;

4 . Summarize SQL queries to count total transactions by type

Use conditional aggregation for counts and revenue: wrap conditions inside SUM(CASE WHEN … THEN value ELSE 0 END). Aggregate once for table-level metrics (total rows, distinct users, paid ≥ 100), and separately rank products by paid revenue with ORDER BY SUM(CASE WHEN status='paid' THEN amount END). Finally, UNION ALL the four answers into a two-column result (question_id, answer), casting numerics to text to match the required schema. It’s a straightforward SQL group by sum with condition scenario in payments or e-commerce analytics.

WITH agg AS (
  SELECT
    -- Q1: total transactions
    SUM(CASE WHEN 1=1 THEN 1 ELSE 0 END)                          AS total_txns,
    -- Q2: distinct users
    COUNT(DISTINCT user_id)                                        AS distinct_users,
    -- Q3: paid txns with amount >= 100
    SUM(CASE WHEN status = 'paid' AND amount >= 100 THEN 1 ELSE 0 END) AS paid_ge_100
  FROM annual_payments
),
top_prod AS (
  -- Q4: product with highest revenue (paid only)
  SELECT product
  FROM annual_payments
  GROUP BY product
  ORDER BY SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) DESC
  LIMIT 1
)
SELECT 1 AS question_id, CAST(total_txns AS TEXT)      AS answer FROM agg
UNION ALL
SELECT 2,              CAST(distinct_users AS TEXT)     FROM agg
UNION ALL
SELECT 3,              CAST(paid_ge_100 AS TEXT)        FROM agg
UNION ALL
SELECT 4,              product                          FROM top_prod
ORDER BY question_id;

Tip: Practice with the Interview Query SQL Question Bank for continuous improvement—filter by topic/difficulty, drill real scenarios, and track progress.

How does SUM(CASE WHEN) differ from SUMIF?

SUMIF is a spreadsheet function; SQL doesn’t have a native SUMIF. When people say SQL SUMIF they mean a SQL conditional sum using SUM(CASE WHEN … THEN value ELSE 0 END). Some dialects offer alternatives (e.g., PostgreSQL’s SUM(value) FILTER (WHERE …)), but the idea is the same: sum only rows meeting a condition.

Can you use multiple conditions inside CASE WHEN?

Yes. Combine predicates with AND / OR, or layer several conditional sums in one query (e.g., one column per segment). This is exactly what folks mean by SUMIF in SQL with multiple rules—compact, readable SQL conditional sum metrics computed in a single grouped scan.

What happens if you omit ELSE 0?

When a condition is false, the CASE returns NULL, and SUM() ignores NULL. Your total will silently undercount. Always include ELSE 0 (and consider COALESCE(value,0)) so the result is deterministic and accurate.

Is SUM(CASE WHEN) faster than filtering rows?

If you need several metrics with different conditions, yes—one grouped pass with multiple conditional sums usually beats running many filtered queries. If you only need a single metric, a simple WHERE with one SUM() can be just as fast. Either way, performance hinges on data grain, indexes, and predicate sargability, not the syntax alone.

When should I use SUM vs COUNT in SQL?

Cumulative sums and conditional aggregation are bread-and-butter skills for real analytics work and appear constantly in interviews. Mastering SUM() OVER (PARTITION BY … ORDER BY …) for running totals and SUM(CASE WHEN … THEN … ELSE 0 END) for conditional KPIs lets you answer business questions in one pass, at the right grain, with clear, auditable SQL.

• Window functions are your default for running totals; self-joins/subqueries are legacy fallbacks.
• Always make ordering deterministic and handle ties/nulls explicitly to avoid silent errors.
• Keep measures readable: pre-aggregate to the correct grain, use clear aliases/CTEs, and include ELSE 0 in conditional sums.

If you want to deepen your understanding, check out these related guides:

• SQL Group By Guide
• SQL Left Join Guide
• SQL Window Functions

Practice Next

Struggling with tricky CASE WHEN logic or GROUP BY filters? Master them with our step-by-step SQL Learning Path — go from basic joins to advanced optimization. Then simulate real pressure with Mock Interviews.

If you need 1:1 guidance on your interview strategy, we can help. Explore Interview Query’s Coaching Program to work with a mentor, refine your prep, and build confidence.