How to Use CASE WHEN With SUM() in SQL

This article will teach you what a CASE WHEN expression is in SQL and how to use it with a SUM() function and a GROUP BY statement. The examples are included to bolster your understanding.

The best way to learn about CASE WHEN and how to use it with SUM() is our hands-on course Creating Basic SQL Reports. It contains over 90 interactive exercises that will teach you different techniques how to create complex reports in SQL.

Knowing how to use a CASE WHEN expression in SQL adds insight into the exciting possibilities in SQL. It elevates you from a simple user who depends on what SQL returns, to a user who is more in command and who sets specific criteria in SQL. Combined with other functions and statements, such as SUM() and GROUP BY, the CASE WHEN expression is a powerful tool for creating complex SQL reports.

CASE, CASE WHEN, CASE WHEN THEN, or CASE WHEN THEN ELSE?

Strictly speaking, it is called a CASE statement in SQL. Sometimes, it is called a CASE WHEN expression or any of the others I’ve mentioned above. Don’t let it confuse you; it’s all the same. Clauses WHEN, THEN, and ELSE are all part of the CASE statement.

How Does a CASE WHEN Expression Work?

You can think of it as an SQL equivalent of the IF-THEN-ELSE construct. You may already be familiar with it, especially if you have used it in Excel or some other programming language. In any case, let’s review it here.

The IF-THEN-ELSE construct goes like this. The IF statement runs a logical test; it checks whether a specific expression is true or false. If the expression is true, it will then assign a certain value you specify; else, it will assign a different value which you also specify.

The structure of the CASE WHEN expression is the same. It runs a logical test; in the case when the expression is true, then it will assign a specific value to it. Else it will assign a different value.

Let me show you the logic and the CASE WHEN syntax in an example.

Example 1: The CASE WHEN Expression

We have a table named test_result which contains test scores. The columns are:

• id: the ID of the student.
• first_name: the first name of the student.
• last_name: the last name of the student.
• score: the test score.

The task is to assign test result categories according to the score. Here’s how to do it:

SELECT	
  first_name,
  last_name,
  score,
  CASE
    WHEN score > 90 THEN 'Exceptional result'
    WHEN score > 70 THEN 'Great result'
    WHEN score > 50 THEN 'Average result'
    ELSE 'Poor result'
  END AS score_category
FROM test_result
ORDER BY score DESC;

The SELECT statement selects the first and the last names of the students and their test scores from the table test_result; nothing unusual here. But then the fun part starts! The CASE statement starts with the keyword CASE, naturally. After that, I define the conditions to be checked by the CASE statement and the values to be assigned; to do that, I use WHEN and THEN. For example, if the score is more than 90, it is categorized as an “Exceptional result.” If it’s more than 70, it is a “Great result.” You don’t have to state “and less than 90” explicitly; SQL takes other conditions into account to avoid duplicated results or errors.

Following the same logic, every score above 50 is considered an “Average result.” Any score not satisfying any of the above three conditions is categorized as a “Poor result”; remember, ELSE is used to assign the value when none of the conditions defined by CASE and WHEN are met.

A CASE statement is closed by an END. After that, you can define the name of the column in which the results of your CASE statement will be stored; in this case, it’s the column score_category. I’ve also ordered the results in descending order by the column score. I’m sure you want to see the result:

Note that an ELSE condition is not mandatory in a CASE statement. Let’s try to omit it. Here’s the same code as the above but without the ELSE condition:

SELECT	
  first_name,
  last_name,
  score,
  CASE
    WHEN score > 90 THEN 'Exceptional result'
    WHEN score > 70 THEN 'Great result'
    WHEN score > 50 THEN 'Average result'
  END AS score_category
FROM test_result
ORDER BY score DESC;

Run the code and examine its result:

The code did run without an error, but the result is different. There’s no “Poor result” category anymore. Instead, there are NULL values. Remember: when the rows don’t match any of the conditions defined, the CASE statement will return NULL values.

If you want to delve more into the syntax, this very thorough article on the CASE WHEN logic could be quite helpful.

Example 2: The CASE WHEN Expression With a SUM() and a GROUP BY

Let’s get serious now! A CASE WHEN expression is often used with a SUM() function in more complex reports, which can be quite challenging for beginners. Even though you’re probably used to using the SUM() function for summing values, it can also be used for counting.

This example will help you understand the concept better. I’ll use the table subject which has the following columns:

• id: the ID of the subject.
• name: the name of the subject.
• number_of_lectures: the number of lectures throughout the year.
• department: the department where the subject is taught.

The task is to count the number of both mandatory and elective subjects by department. In this example, every subject that has more than 20 lectures during the year is considered mandatory. Do you know how to solve this task? Here, let me help you:

SELECT	
  department,
  SUM (CASE
    WHEN number_of_lectures > 20 THEN 1
    ELSE 0
  END) AS mandatory_subjects,
  SUM (CASE
    WHEN number_of_lectures <= 20 THEN 1
    ELSE 0
  END) AS elective_subjects
FROM subject
GROUP BY department;

Let’s analyze the code! To start, it selects the column department from the table subject. Then comes the curious use of a SUM() with a CASE WHEN. This expression says whenever the number_of_lectures is higher than 20, the row is assigned the value 1. If the condition is not met, the assigned value is 0.

The SUM() function will sum all those rows that have the assigned value equal to 1. Think for a moment; if you add all the 1s, what will you get? Exactly, it’s the same as if you’d counted the rows whose number_of_lectures is above 20. Using a CASE WHEN expression to assign values 0 or 1 to the table rows is just a little trick to make SUM() return the number of rows just like the COUNT() function would. The number of the subject with more than 20 lectures will be shown in the column mandatory_subjects.

The same logic applies to the next CASE WHEN expression. The only difference is that the condition refers to 20 or fewer lectures, with the result shown in the column elective_subjects.

Finally, the result of the query is grouped by the column department. Here’s what this query will return as a result:

If you’re having trouble with GROUP BY, this article which explains its logic always comes in handy. And if you’re looking for some nice examples, here’s a piece that gives you five examples of GROUP BY.

Let me show you one more example of the CASE WHEN expression. They say practice makes perfect. Well, that’s not entirely true; perfection does not exist! It may not make you perfect, but practice will surely make you better at code writing.

Example 3: Using a CASE WHEN Expression With a SUM() and a GROUP BY

In this final example, I will use the table orders. It contains the following columns:

• id: the ID of the order.
• total_price: the total price of the order.
• order_date: the date of the order.
• status: the status of the order.
• ship_country: the country where the order has to be shipped to.

Your task is to show the number of shipped orders by country. The order is shipped if its status is “shipped” or “delivered.” This code will get you the desired result:

SELECT 
  ship_country,
  SUM(CASE
    WHEN status = 'shipped' OR status = 'delivered' THEN 1
    ELSE 0
  END) AS order_shipped
FROM orders
GROUP BY ship_country;

The code selects the column ship_country from the table orders. It then uses a CASE WHEN expression to assign the value 1 to all the rows with the status “shipped” or “delivered.” All other statuses will have the value 0 assigned to them. The new column is named order_shipped. Finally, the result is grouped by the column ship_country. Here’s the result:

Starting to Unlock the Possibilities of the CASE WHEN Expression?

I hope this article helped you understand the CASE WHEN expression in general by discussing its logic and showing you examples of how it works. Using the CASE WHEN expressions can really unlock the possibilities of SQL. It is made even more powerful when combined with aggregate functions such as SUM(), which I showed in two examples.

You can learn even more in the course Creating Basic SQL Reports. Read about the course in an episode of our Course of the Month series. For more SQL practice, check out our SQL Practice. It has over 500 SQL exercises and we keep adding more!