How To Use CASE WHEN With SUM: A Full SQL Guide (Updated for 2025)

SQL (Structured Query Language) is one of the most important querying languages in use today to access and transform relational databases. A common SQL problem statement is to perform a database manipulation based on conditional logic.

The CASE WHEN statement provides flexibility in expressing conditional logic as it can handle multiple conditions. To aggregate with conditions, the SUM CASE WHENfunction in SQL can achieve optimized results.

In this article, we’ll guide you to use SUM() and CASE() functions, some common use cases, and how the two functions can be nested. We will also explore some real-life examples for practice and provide a few useful tips to ensure error-free, optimized code.

The CASE function will help you transform data in SQL with conditional logic, much like an IF-ELSE statement.

The CASE expression goes through several conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

Example:

Let’s say you’ve been asked to categorize employees based on their yearly pay.

EmployeeID	EmployeeName	Salary
1	John Doe	55000
2	Jane Smith	60000
3	Alice Johnson	120000
4	Bob Williams	45000
5	Eva Davis	120000

Here is the query to achieve the given operation.

SELECT
    EmployeeName,
    Salary,
    CASE
        WHEN salary >= 50000 AND salary < 70000 THEN 'Grade A'
        WHEN salary >= 70000 AND salary < 90000 THEN 'Grade B'
        WHEN salary >= 90000 THEN 'Grade C'
        ELSE 'Grade D'
    END AS Grade
FROM
    employees;

Here is the desired result:

EmployeeID	EmployeeName	Salary	Grade
1	John Doe	55000	Grade A
2	Jane Smith	60000	Grade A
3	Alice Johnson	120000	Grade B
4	Bob Williams	45000	Grade D
5	Eva Davis	120000	Grade C

In this example, the CASE statement handles multiple conditions to categorize employees into different pay grades. Each WHEN condition checks a specific range of salaries and assigns the corresponding pay grade. The ELSE statement handles all other conditions that are not included in the WHEN conditions.

The SUM function in SQL is an aggregate function used for calculating the total sum of values in a specific column. It is most often used with the following:

• GROUP BY clause to calculate totals within groups.
• DISTINCT to calculate the sum of distinct values in a column
• other arithmetic operations such as COUNTto perform complex calculations.

Note that SUMignores NULL values. If a row contains a NULL value in the column being summed, it is not included in the calculation. This feature can help when handling missing data.

Using SUM with CASE WHEN allows you to perform conditional summation in SQL. It’s particularly useful when you want to aggregate values based on specific conditions. Consider it the equivalent of “pivoting” data - combining multiple rows by category and an aggregate function.

Syntax:

SUM(CASE WHEN condition THEN value ELSE alternative END)

In this syntax:

• condition: The criteria that must be met for the THEN clause to execute.
• value: The value to sum if the condition is met.
• THEN: Specifies the result if the condition evaluates to true.
• ELSE alternative: If the condition is not met, this value is used. Often set to 0 so it doesn’t affect the sum.
• END: Concludes the CASE expression.

Example:

SELECT
  SUM(CASE WHEN column_name > 100 THEN column_name ELSE 0 END) AS conditional_sum
FROM
  table_name;

In this example, we’re summing all the values in column_name where each value is greater than 100. If a value is 100 or less, it does not contribute to the sum.

Now, for the above illustration, the same result can be achieved more efficiently by excluding the rows with values 100 or less with the WHERE clause. However, combining SUM with CASE-WHEN is necessary when:

• Weighted calculations: You want to assign different weightage to the values in different rows, according to multiple conditions.
• Conditional aggregation: Sometimes, you need to aggregate data conditionally in different ways within the same column. The CASE WHEN inside SUM allows you to differentiate the sums based on varying conditions. It enables you to avoid multiple queries or subqueries and allows for easier readability and maintenance of reporting.
• Preserving row count: Using a WHERE clause will change the count of the rows returned. With SUM and CASE WHEN, you can maintain the integrity of the row count, which is particularly useful in reports where you want to show both the total and the conditional sums.
• Aggregating partitioned data: When used with GROUP BY or window functions, SUM, and CASE WHEN can produce aggregates over partitions of the data, which isn’t possible with WHERE alone when multiple clauses need to be satisfied.

Here are some real-life scenarios where these two functions should be nested:

• Segmenting sales: Say a retail company wants a break-up of revenue according to multiple product statuses. If products have various status types like ‘active’, ‘discontinued’, and ‘back order’, you can aggregate revenue using the SUM and CASE statements.
• Time-based conditional summation: Let us assume you work with a marketing agency, and they want to compare the impact of several campaigns run on sales. You can calculate the total sales per campaign by applying the conditional logic based on the campaign dates.
• Tracking performance metrics: In a table tracking the performance of sales representatives, you might want to calculate the total number of sales above a certain amount, which qualifies for a bonus.
• Customer segmentation: An e-commerce firm may want to find the total number of customers based on their spending, categorizing them as ‘low’, ‘medium’, and ‘high’ spenders to personalize campaigns targeting each group.

Let us explore some real-world examples on this topic with written code.

1. SUM with CASE WHEN calculating the total amount for two categories of items

You’re working with an e-commerce company. You want to calculate the total sales amount for two categories of items: ‘Electronics’ and ‘Clothing’, as well as a total for all other categories. Use SUM with CASE WHEN to solve the problem.

Let’s say your sales table looks like this:

item_id	category	amount
1	Electronics	200.00
2	Electronics	450.00
3	Clothing	50.00
4	Clothing	75.00
5	Books	20.00
6	Groceries	30.00

This is the code to pivot the data by sales category:

SELECT
  SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS ElectronicsSales,
  SUM(CASE WHEN category = 'Clothing' THEN amount ELSE 0 END) AS ClothingSales,
  SUM(CASE WHEN category NOT IN ('Electronics', 'Clothing') THEN amount ELSE 0 END) AS OtherSales
FROM
  sales;

And here is the expected output:

ElectronicsSales	ClothingSales	OtherSales
650.00	125.00	50.00

You can practice a similar interview problem here: Monthly Product Sales

2. SUM with CASE WHEN and the GROUP BY clause

Let’s look at a slightly more complex problem: SUM with CASE WHEN and the GROUP BY clause. Suppose we have a table of transactions with each transaction belonging to different departments within a company. We want to calculate the total spend for ‘IT’, ‘HR’, and ‘Marketing’, and also have a total for ‘Other’ departments, grouped by quarters.

Let us assume that you are supplied with the following transactions table.

transaction_id	department	amount	transaction_date
1	IT	1000.00	2023-01-15
2	IT	1500.00	2023-02-17
3	HR	750.00	2023-04-25
4	Marketing	1250.00	2023-05-11
5	Marketing	1350.00	2023-07-19
6	Finance	2000.00	2023-08-21
7	HR	800.00	2023-10-05
8	IT	1100.00	2023-11-12

We will group by the quarters of the transaction dates. The quarters are:

• Q1: January - March
• Q2: April - June
• Q3: July - September
• Q4: October - December

Here is the code to achieve this transformation.

SELECT
  CASE 
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 7 AND 9 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 10 AND 12 THEN 'Q4'
  END AS Quarter,
  SUM(CASE WHEN department = 'IT' THEN amount ELSE 0 END) AS ITSales,
  SUM(CASE WHEN department = 'HR' THEN amount ELSE 0 END) AS HRSales,
  SUM(CASE WHEN department = 'Marketing' THEN amount ELSE 0 END) AS MarketingSales,
  SUM(CASE WHEN department NOT IN ('IT', 'HR', 'Marketing') THEN amount ELSE 0 END) AS OtherSales
FROM
  transactions
GROUP BY
  Quarter;

The above query uses the CASE WHEN statement in two ways; one, simply to categorize the transaction dates by quarters, and the other way is by nesting the sum and case statement to aggregate the spend by department. The output from the above query would look like this:

Quarter	ITSales	HRSales	MarketingSales	OtherSales
Q1	2500.00	0.00	0.00	0.00
Q2	0.00	750.00	1250.00	0.00
Q3	0.00	0.00	1350.00	2000.00
Q4	1100.00	800.00	0.00	0.00

This table shows the total sales amount for each department, grouped by the fiscal quarter. The ‘OtherSales’ column includes sales from departments not listed specifically (like ‘Finance’ in the transactions table).

3. SUM with CASE WHEN in addition to an arithmetic operation

HR Analytics Case Study: In this example, we’ll look at SUM with CASE WHEN in addition to an arithmetic operation. The HR in your organization wants to calculate employees’ total adjusted salaries in order to track spend per department/job role. The adjusted salary calculation includes a performance-based bonus.

Performance Bonus Criteria:

• Excellent: 10% bonus on base salary.
• Good: 5% bonus on base salary.
• Average or below: No bonus.

The following employees table is provided to you:

employee_id	role	base_salary	performance_rating
1	Software Developer	70000.00	Excellent
2	Software Developer	68000.00	Good
3	HR Manager	65000.00	Average
4	Sales Representative	48000.00	Excellent
5	Sales Representative	45000.00	Good
6	HR Manager	47000.00	Excellent

While calculating the adjusted pay, we’ll include the CASE WHEN statement to apply conditional logic and compute remuneration accordingly. Here is the code to get the desired result:

SELECT 
    role,
    SUM(
        CASE 
            WHEN performance_rating = 'Excellent' THEN base_salary * 1.10
            WHEN performance_rating = 'Good' THEN base_salary * 1.05
            ELSE base_salary
        END
    ) AS TotalAdjustedSalary
FROM 
    employees
GROUP BY 
    role;

The output should look like this:

role	TotalAdjustedSalary
Software Developer	148400.00
HR Manager	116700.00
Sales Representative	100050.00

The CASE WHEN statement in the query adds a conditional bonus to total compensation by job type based on their job title.

4. SUM with CASE WHEN statement when used with a logical operator

We’ll look at one last example - the SUM with CASE WHEN statement when used with a logical operator. An electronics retail company tracks its sales and wants to run a special performance analysis. They want to categorize sales into different bonus eligibility groups based on the amount of sales and the region where the sale occurred. The conditions for categorization are:

• Premium Sales: Any sales that are over or equal to $2000, or any sales in the “East” region regardless of the amount, except during July, are considered premium. These sales get a higher commission.
• Standard Sales: Any sales below $2000, except those in the “East” region, are considered standard, except in July. They get a standard commission.
• Promotional Sales: All sales made in the promotional period (July), regardless of the amount and region, are considered promotional and are eligible for special discounts or offers in future transactions.

The company wants a report that sums these amounts by region to help with financial planning and bonus allocations.

Here is the sales table:

sale_id	employee_id	product_id	sale_amount	sale_date	region
1	101	30	1200	2023-06-15	East
2	102	22	800	2023-06-17	West
3	103	11	2300	2023-06-25	East
4	101	45	400	2023-07-03	North
5	102	30	1800	2023-07-19	East
6	104	25	1500	2023-07-21	South
7	103	22	900	2023-08-05	West
8	101	11	1600	2023-08-12	East

And here is the code to get the required output.

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;

The output would group sales according to the company’s specifications:

region	Total_Sales	Promotional_Sales	Premium_Sales	Standard_Sales
East	6900	1800	5100	0
North	400	400	0	0
South	1500	1500	0	0
West	1700	0	0	1700

Likewise, you can combine the SUM and CASE statements with other logical operators such as BETWEEN and AND in a similar way to incorporate conditional logic in similar scenarios.

5. SUM with CASE WHEN Handling NULL Values

This example demonstrates how to handle NULL values in conditional aggregation to accurately count and categorize data.

Input Table: sales

item_id	category	amount
1	Electronics	200.00
2	NULL	450.00
3	Clothing	50.00
4	NULL	75.00
5	Books	20.00
6	Groceries	30.00

Query:

SELECT 
    SUM(CASE WHEN category IS NULL THEN 1 ELSE 0 END) AS NullCategoriesCount,
    SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END) AS ValidCategoriesCount
FROM sales;

Result Table:

NullCategoriesCount	ValidCategoriesCount
2	4

6. SUM with CASE WHEN for Weighted Aggregation

This example shows how to calculate weighted sales based on region, applying a multiplier to the sales amount in specific regions.

Input Table: sales

sale_id	region	sales_amount
1	North	1000.00
2	South	2000.00
3	East	1500.00
4	North	1200.00

Query:

SELECT 
    SUM(CASE WHEN region = 'North' THEN sales_amount * 1.2 ELSE sales_amount END) AS WeightedSales
FROM sales;

Result Table:

WeightedSales
5400.00

7. SUM with CASE WHEN for Dynamic Date Ranges

This example demonstrates how to calculate total sales for specific date ranges, such as quarterly sales.

Input Table: sales

sale_id	sale_date	sales_amount
1	2025-01-15	1000.00
2	2025-02-17	1500.00
3	2025-04-25	750.00
4	2025-05-11	1250.00

Query:

SELECT 
    SUM(CASE WHEN sale_date BETWEEN '2025-01-01' AND '2025-03-31' THEN sales_amount ELSE 0 END) AS Q1Sales,
    SUM(CASE WHEN sale_date BETWEEN '2025-04-01' AND '2025-06-30' THEN sales_amount ELSE 0 END) AS Q2Sales
FROM sales;

Result Table:

Q1Sales	Q2Sales
2500.00	2000.00

8. SUM with CASE WHEN Using Multiple Conditions

This example shows how to combine multiple conditions within the CASE WHEN statement to aggregate data based on more specific criteria.

Input Table: sales

sale_id	product_type	region	sales_amount
1	Electronics	West	1000.00
2	Electronics	East	1500.00
3	Clothing	West	500.00

Query:

SELECT 
    SUM(CASE WHEN product_type = 'Electronics' AND region = 'West' THEN sales_amount ELSE 0 END) AS WestElectronicsSales
FROM sales;

Result Table:

WestElectronicsSales
1000.00

The SUM with CASE WHEN construct is quite standard in SQL and is typically supported across most SQL databases with no variation in syntax.

However, in MySQL, you have the option to use the IF() function. It works similarly to programming languages’ IF-ELSE statements and can only be used in SELECT statements, WHERE clauses, and other places where you would use a MySQL expression.

Here’s how you might nest an IF() function with SUM() to perform conditional summation.

SELECT 
  SUM(IF(category IN ('A', 'B'), amount, 0)) AS TotalSales
FROM sales;

While the IF() function has an easier syntax to follow, it is important to remember CASE is part of the SQL standard and is supported by virtually all SQL-compliant RDBMS, whereas IF() is specific to MySQL. If you want to ensure that your skills are transferable, you should prefer CASE. Additionally, CASE can handle multiple conditions in a sequence making it more versatile. So, it is generally recommended to use CASE-WHEN over IF-ELSE even when querying in MySQL.

It is important to remember the following caveats to make your code error-free and efficient:

• Ordering conditional logic: The ordering of conditions in a CASE expression is significant, as the conditions are evaluated in the order they are written. Once a condition is met, the CASE expression will return the corresponding result and not evaluate any subsequent conditions. This downside is important to bear in mind to ensure that your query produces the correct result.
• Handling NULLs: The CASE expression within an aggregate function like SUM must be carefully written to handle NULL values appropriately, as SUM disregards rows with NULLs.
• Efficiency: The CASE statement is an additional operation on each row, which can introduce overhead, especially for a large dataset. Use a WHERE clause to limit the rows whenever possible before implementing CASE logic. This can help the query planner to use an index to filter rows first, thus reducing the number of rows over which the CASE needs to be evaluated.

SUM with CASE WHEN allows you to conditionally aggregate data, making it a flexible tool for various analytical tasks. In simple terms, it is a way to sum integer values based on specific, customizable conditions.

These examples guides you how to use SUM CASE WHEN to categorize sales, group transactions by fiscal quarters, adjust salaries based on performance, and analyze sales based on complex logical criteria. These techniques can efficiently transform data directly within your SQL queries.

We hope that this article has helped you understand the different ways to combine the SUM and CASE-WHEN functions to perform powerful transformations in SQL.

For an end-to-end overview of SQL, including complex joins, advanced reporting, and creating multi-table databases, you can explore our specialized learning path.

Related articles:

• SQL LEFT JOIN to combine multiple rows
• Weighted average in SQL
• Using SQL COUNT with CASE WHEN
• VLOOKUP in SQL

More SQL guides:

• The ultimate SQL cheat sheet for interview success
• SQL conditional join: a comprehensive guide
• SQL cumulative sum: a comprehensive guide