{"id":3852,"date":"2023-11-11T15:06:40","date_gmt":"2023-11-11T15:06:40","guid":{"rendered":"https:\/\/www.copahost.com\/blog\/?p=3852"},"modified":"2023-11-17T19:35:00","modified_gmt":"2023-11-17T19:35:00","slug":"case-when-sql","status":"publish","type":"post","link":"https:\/\/www.copahost.com\/blog\/case-when-sql\/","title":{"rendered":"CASE WHEN SQL: What is it and how to use it?"},"content":{"rendered":"\n<p>CASE WHEN is one of the most powerful and versatile functions in SQL, allowing you to perform advanced operations on your data tables.&nbsp;This way, it is possible to perform&nbsp;<strong>selection, update, insertion and classification operations according to specific rules<\/strong>&nbsp;defined by the user.<\/p>\n\n\n\n<p>However, many people still don&#8217;t quite understand how CASE WHEN works or how to apply it to their own applications.&nbsp;In this sense, this function can be used in any SQL&nbsp;database.&nbsp;For example, a MySQL&nbsp;database&nbsp;present in&nbsp; <a href=\"https:\/\/www.copahost.com\/en\/\">Copahosting website<\/a>.<\/p>\n\n\n\n<p>In this article, we will explore what CASE WHEN is.&nbsp;Also, how it works and how to use it to improve your data management.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_69_1 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#What_is_CASE_WHEN_in_SQL_and_how_does_it_work\" title=\"What is CASE WHEN in SQL and how does it work?\">What is CASE WHEN in SQL and how does it work?<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#Syntax\" title=\"Syntax\">Syntax<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#How_to_use_CASE_WHEN_to_perform_selection_operations_on_a_table\" title=\"How to use CASE WHEN to perform selection operations on a table?\">How to use CASE WHEN to perform selection operations on a table?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#How_to_use_CASE_WHEN_to_perform_update_operations_on_a_table\" title=\"How to use CASE WHEN to perform update operations on a table?\">How to use CASE WHEN to perform update operations on a table?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#How_to_use_CASE_WHEN_SQL_to_perform_insertion_operations_on_a_table\" title=\"How to use CASE WHEN SQL to perform insertion operations on a table?\">How to use CASE WHEN SQL to perform insertion operations on a table?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#How_to_use_CASE_WHEN_to_perform_sort_operations_on_a_table\" title=\"How to use CASE WHEN to perform sort operations on a table?\">How to use CASE WHEN to perform sort operations on a table?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#Comparison_between_CASE_WHEN_and_other_selection_functions_in_SQL\" title=\"Comparison between CASE WHEN and other selection functions in SQL.\">Comparison between CASE WHEN and other selection functions in SQL.<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#Practical_examples_of_using_CASE_WHEN_SQL\" title=\"Practical examples of using CASE WHEN SQL\">Practical examples of using CASE WHEN SQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#1_Sales_Analysis\" title=\"1. Sales Analysis:\">1. Sales Analysis:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#2_Credit_rating\" title=\"2. Credit rating:\">2. Credit rating:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#3_Spending_analysis\" title=\"3. Spending analysis:\">3. Spending analysis:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#4_Product_Classification\" title=\"4. Product Classification:\">4. Product Classification:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#5_Sales_analysis_by_category\" title=\"5. Sales analysis by category:\">5. Sales analysis by category:<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_CASE_WHEN_in_SQL_and_how_does_it_work\"><\/span>What is CASE WHEN in SQL and how does it work?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>CASE WHEN in SQL is a conditional function that allows you to define specific rules for operations on a data table.&nbsp;Thus, we use it when a specific condition is true.&nbsp;The CASE WHEN is composed of three parts: the \u201c&nbsp;<strong>CASE<\/strong>&nbsp;\u201d clause, the \u201c&nbsp;<strong>WHEN<\/strong>&nbsp;\u201d clause and the \u201c&nbsp;<strong>THEN<\/strong>&nbsp;\u201d clause.<\/p>\n\n\n\n<p>Thus, we use the \u201c&nbsp;<strong>CASE<\/strong>&nbsp;\u201d clause to start the CASE WHEN statement.&nbsp;<strong>Therefore, we apply the \u201c WHEN<\/strong>&nbsp;\u201d clause&nbsp;to define the conditions that must be true for the action to be executed.&nbsp;And finally, we use the \u201c&nbsp;<strong>THEN<\/strong>&nbsp;\u201d clause to specify the action that should be performed when the condition is true.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Syntax\"><\/span>Syntax<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>CASE WHEN syntax in SQL is simple and easy to understand.&nbsp;Thus, it is composed of three main parts: the \u201cCASE\u201d clause, the \u201cWHEN\u201d clause and the \u201cTHEN\u201d clause.<\/p>\n\n\n\n<p>In this sense, we use the \u201cCASE\u201d clause to start the CASE WHEN statement and we can write it as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CASE<\/code><\/pre>\n\n\n\n<p>We use the \u201cWHEN\u201d clause to define the conditions that must be true for the action to be performed.&nbsp;Therefore, it is written as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WHEN condition<\/code><\/pre>\n\n\n\n<p>Where we use the \u201cTHEN\u201d clause to specify the action we should perform when the condition is true.&nbsp;So, it is written as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>THEN action<\/code><\/pre>\n\n\n\n<p>The complete CASE WHEN syntax in SQL can be written as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CASE condition\nWHEN conditional_values\nTHEN actions\nELSE default_action\nEND<\/code><\/pre>\n\n\n\n<p>Where:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u201ccondition\u201d refers to the condition that must be true for the action to be executed;<\/li>\n\n\n\n<li>\u201cconditional_values\u201d are the conditional values \u200b\u200bthat are evaluated to determine the action that is being executed;<\/li>\n\n\n\n<li>\u201cactions\u201d are the actions that will be executed when the condition is true;<\/li>\n\n\n\n<li>\u201cdefault_action\u201d refers to the action that is being executed when the condition is false.<\/li>\n<\/ul>\n\n\n\n<p>Therefore, we see flexibility and adaptation to the specific needs of each application in the CASE WHEN syntax.<\/p>\n\n\n\n<p>Next we will see how we can apply CASE WHEN to perform select, update, insert and sort operations on a data table.&nbsp;For example, you can use CASE WHEN to select only records that meet a certain condition.&nbsp;Or, to update the value of a column based on another column.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_use_CASE_WHEN_to_perform_selection_operations_on_a_table\"><\/span>How to use CASE WHEN to perform selection operations on a table?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We can also apply the CASE WHEN function to perform selection operations on a data table.&nbsp;So, we need to define a condition that is true for a given record.&nbsp;Then specify the action that should be performed when this condition is true.<\/p>\n\n\n\n<p>There are some differences between SQL&#8217;s CASE WHEN and&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/python-switch-case\/\">Python&#8217;s SWITCH CASE<\/a>&nbsp;.<\/p>\n\n\n\n<p>For example, assuming we have a \u201csales\u201d table with the following columns: id, product, value and date.&nbsp;We can use CASE WHEN to select only records where the value of the \u201cproduct\u201d column is equal to \u201ccomputer\u201d.&nbsp;The syntax for this would be as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM sales\nWHERE CASE WHEN product= 'computer' THEN 1 ELSE 0 END = 1<\/code><\/pre>\n\n\n\n<p>In this example, we are using the \u201cSELECT\u201d clause to select all columns from the \u201csales\u201d table.&nbsp;Next, we are using the \u201cWHERE\u201d clause to select only those records where the \u201cproduct\u201d column value equals \u201ccomputer\u201d.&nbsp;Thus, we use CASE WHEN to define the condition \u201cproduct = &#8216;computer&#8217;\u201d, and we use the column \u201c1\u201d to indicate that the condition is true.<\/p>\n\n\n\n<p>Thus, the query above will select all records from the \u201csales\u201d table in which the value of the \u201cproduct\u201d column is equal to \u201ccomputer\u201d.<\/p>\n\n\n\n<p>It is important to note that CASE WHEN we also use to select multiple conditions and to select fields calculated from other columns.&nbsp;The syntax is the same, the only difference is the amount of \u201cWHEN\u201d you want to use.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_use_CASE_WHEN_to_perform_update_operations_on_a_table\"><\/span>How to use CASE WHEN to perform update operations on a table?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>CASE WHEN can also be applied to perform update operations on a data table.&nbsp;In this sense, we define a condition that is true for a given record.&nbsp;We then specify the action that should be performed when this condition is true.<\/p>\n\n\n\n<p>For example, assuming we have a \u201ccustomers\u201d table with the following columns: id, name, email and age.&nbsp;We can use CASE WHEN to update the value of the \u201cage\u201d column.&nbsp;Only for records in which the value of the \u201cage\u201d column is less than 18. The syntax for this would be as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE customers\nSET age = CASE WHEN age &lt; 18 THEN 18 ELSE age END\nWHERE age &lt; 18<\/code><\/pre>\n\n\n\n<p>In this example, we are using the \u201c&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/update-sql\/\">UPDATE<\/a>&nbsp;\u201d clause to update the \u201cage\u201d column of the \u201ccustomers\u201d table.&nbsp;Next, we are using the \u201cSET\u201d clause to specify that the \u201cage\u201d column should be updated with the value of the \u201cage\u201d column when it is less than 18. CASE WHEN is used to define the \u201cage &lt; 18\u201d condition , and the \u201cage\u201d column is used to specify the value that should be updated.<\/p>\n\n\n\n<p>Therefore, the above query will only update the records in the \u201ccustomers\u201d table.&nbsp;Only where the value of the \u201cage\u201d column is less than 18. The value of the \u201cage\u201d column will be updated to 18.<\/p>\n\n\n\n<p>It is important to note that CASE WHEN can also be used to update multiple columns.&nbsp;Also to update fields calculated from other columns.&nbsp;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_use_CASE_WHEN_SQL_to_perform_insertion_operations_on_a_table\"><\/span>How to use CASE WHEN SQL to perform insertion operations on a table?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We can apply CASE WHEN to perform insertion operations on a data table.&nbsp;In this sense, we need to define a condition that will be true for a given record.&nbsp;And then, we specify the action that will be executed when this condition is true.<\/p>\n\n\n\n<p>For example, assuming we have a \u201cproducts\u201d table with the following columns: id, name, price and category.&nbsp;So, we can apply CASE WHEN to insert a new record into the table.&nbsp;Only if the value of the \u201ccategory\u201d column is equal to \u201celectronic\u201d.&nbsp;The syntax for this would be as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO products (name, price, category)\nVALUES ('LCD TV', 1200, CASE WHEN category = 'electronic' THEN 'electronic' ELSE 'other' END)<\/code><\/pre>\n\n\n\n<p>In this example, we are using the \u201cINSERT INTO\u201d clause to insert a new record into the \u201cproducts\u201d table.&nbsp;Next, we are using the \u201cVALUES\u201d clause to specify the values \u200b\u200bof the \u201cname\u201d, \u201cprice\u201d and \u201ccategory\u201d columns.&nbsp;Thus, we use CASE WHEN to define the condition \u201ccategory = &#8216;electronic&#8217;\u201d.&nbsp;Next, we use the \u201celectronic\u201d column to specify the value that should be inserted in the \u201ccategory\u201d column.<\/p>\n\n\n\n<p>Thus, the above query will insert a new record into the \u201cproducts\u201d table.&nbsp;With the values \u200b\u200b\u201cLCD TV\u201d for the \u201cname\u201d field, \u201c1200\u201d for the \u201cprice\u201d field and \u201celectronic\u201d for the \u201ccategory\u201d field.&nbsp;Only if the category is equal to \u201celectronic\u201d.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_use_CASE_WHEN_to_perform_sort_operations_on_a_table\"><\/span>How to use CASE WHEN to perform sort operations on a table?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We can also use CASE WHEN to perform sort operations on a data table.&nbsp;To do this, we need to define a condition that is true for a given record.&nbsp;Next, we specify the action to be performed when this condition is true.<\/p>\n\n\n\n<p>For example, assuming we have a \u201csales\u201d table with the following columns: id, product, value and date.&nbsp;We can use CASE WHEN to sort the records in the table based on the value in the \u201cvalue\u201d column.&nbsp;The syntax for this would be as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT product, value,\n  CASE WHEN value &gt; 100 THEN 'High'\n      WHEN value &gt; 50 THEN 'Medium'\n      ELSE 'Low' END AS rating\nFROM sales<\/code><\/pre>\n\n\n\n<p>In this example, we are using the \u201cSELECT\u201d clause to select the columns \u201cproduct\u201d, \u201cvalue\u201d and a new column called \u201cclassification\u201d.&nbsp;Therefore, we use CASE WHEN to define the condition \u201cvalue &gt; 100\u201d and \u201cvalue &gt; 50\u201d.&nbsp;And we use the \u201cclassification\u201d column to specify the classification of the value.<\/p>\n\n\n\n<p>Thus, the above query will classify the records in the \u201csales\u201d table as \u201cHigh\u201d if the value in the \u201cvalue\u201d column is greater than 100. \u201cMedium\u201d if the value is greater than 50 and \u201cLow\u201d if the value is equal to or less than 50.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Comparison_between_CASE_WHEN_and_other_selection_functions_in_SQL\"><\/span>Comparison between CASE WHEN and other selection functions in SQL.<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The CASE function in SQL allows you to create a new column based on one or more conditions.&nbsp;In SQL, there are several alternatives to the CASE function, including the IIF function, the IF function, and the SWITCH function.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">IIF function<\/h4>\n\n\n\n<p>The IIF function is similar to the CASE function, but it is easier to read and write.&nbsp;The syntax of the IIF function is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IIF(condition, result_if_true, result_if_false)<\/code><\/pre>\n\n\n\n<p>For example, you can create a new column that indicates whether a sale is being made to a loyal customer or not.&nbsp;To do this, you can use the IIF function as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n  id,\n  name,\n  IIF(loyalty = 1, 'yes', 'no') as loyalist\nFROM customers<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">IF function<\/h4>\n\n\n\n<p>The IF function is similar to the CASE function, but it is simpler and does not support as many conditions.&nbsp;The syntax of the IF function is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF(condition, result_if_true, result_if_false)<\/code><\/pre>\n\n\n\n<p>For example, you can create a new column that indicates whether a sale is being made to a loyal customer or not.&nbsp;To do this, you can use the IF function as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n  id,\n  name,\n  IF(loyalty = 1, 'yes', 'no') as loyal\nFROM customers<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">SWITCH function<\/h4>\n\n\n\n<p>The SWITCH function is a new function in SQL that allows you to create a new column based on one or more conditions.&nbsp;The syntax of the SWITCH function is as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SWITCH(condition\n  WHEN value1 THEN result1\n  WHEN value2 THEN result2\n  ...\n  ELSE default_default\nEND<\/code><\/pre>\n\n\n\n<p>For example, you can create a new column that indicates whether a sale is being made to a loyal customer or not.&nbsp;To do this, you can use the SWITCH function as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SWITCH(loyalty\n  WHEN 1 THEN 'yes'\n  WHEN 0 THEN 'no'\n  ELSE 'unknown'\nEND as loyal\nFROM sales<\/code><\/pre>\n\n\n\n<p>In summary, there are several alternatives to the CASE WHEN function in SQL.&nbsp;Thus, each of these functions has its own specific characteristics and uses.&nbsp;Choosing the appropriate function depends on what we need to do with the data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Practical_examples_of_using_CASE_WHEN_SQL\"><\/span>Practical examples of using CASE WHEN SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>CASE WHEN is a data flow control clause that allows you to create a choice structure in SQL.&nbsp;This way, we use it to perform different actions based on different conditions.&nbsp;Here are some practical examples of how we use CASE WHEN in different business scenarios:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_Sales_Analysis\"><\/span>1. Sales Analysis:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>A business can use CASE WHEN to analyze the sales of its products.&nbsp;For example, a business might use the following query to determine the best-selling product of the month:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT ProductName, SUM(QuantitySold) as TotalSales\nFROM Sales\nWHERE Month = MONTH(GETDATE()) AND Year = YEAR(GETDATE())\nGROUP BY ProductName\nORDER BY TotalSales DESC<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Credit_rating\"><\/span>2. Credit rating:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Financial institutions use CASE WHEN to classify their customers&#8217; credit.&nbsp;For example, a financial institution might use the following query to rate a customer&#8217;s credit based on their payment history:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n    CASE\n        WHEN PaymentHistory &gt;= 60 THEN 'A'\n        WHEN PaymentHistory &gt;= 30 AND PaymentHistory &lt; 60 THEN 'B'\n        WHEN PaymentHistory &gt;= 12 AND PaymentHistory &lt; 30 THEN 'C'\n        ELSE 'D'\n    END as CreditRating,\n    CustomerName\nFROM Customers<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_Spending_analysis\"><\/span>3. Spending analysis:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Businesses can use CASE WHEN to analyze their spending and identify areas for cost reduction.&nbsp;For example, a company might use the following query to determine the highest expenses for the month:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT DepartmentName, SUM(Expenses) as TotalExpenses\nFROM Expenses\nWHERE Month = MONTH(GETDATE()) AND Year = YEAR(GETDATE())\nGROUP BY DepartmentName\nORDER BY TotalExpenses DESC<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4_Product_Classification\"><\/span>4. Product Classification:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>A business can use CASE WHEN to classify its products based on their characteristics.&nbsp;For example, a business might use the following query to classify its products based on their category:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n    CASE\n        WHEN Category = 'Electronics' THEN 'A'\n        WHEN Category = 'Clothing' THEN 'B'\n        WHEN Category = 'Home &amp; Garden' THEN 'C'\n        ELSE 'D'\n    END as ProductCategory,\n    ProductName\nFROM Products\nORDER BY ProductCategory<\/code><\/pre>\n\n\n\n<p>In this example, the clause&nbsp;&nbsp;<code>ORDER BY<\/code>&nbsp;is used to order the products based on their category.&nbsp;The first category is Sort A, the second is Sort B, and so on.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5_Sales_analysis_by_category\"><\/span>5. Sales analysis by category:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>A business can use CASE WHEN in conjunction with the&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/?p=3815&amp;preview=true\">GROUP BY<\/a>&nbsp;clause to analyze its sales by category.&nbsp;For example, a business might use the following query to determine total sales for each product category:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n    CASE\n        WHEN Category = 'Electronics' THEN 'A'\n        WHEN Category = 'Clothing' THEN 'B'\n        WHEN Category = 'Home &amp; Garden' THEN 'C'\n        ELSE 'D'\n    END as ProductCategory,\n    SUM(QuantitySold) as TotalSales\nFROM Sales\nGROUP BY ProductCategory\nORDER BY TotalSales DESC<\/code><\/pre>\n\n\n\n<p>In this example, the clause\u00a0\u00a0<code><a href=\"https:\/\/www.copahost.com\/blog\/group-by-sql\/\">GROUP BY<\/a><\/code>\u00a0is used to group sales by product category.\u00a0The clause\u00a0\u00a0<code>ORDER BY<\/code>\u00a0is used to order the results based on total sales in descending order.\u00a0The clause\u00a0\u00a0<code>CASE WHEN<\/code>\u00a0is used to define the product category based on its name.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>CASE WHEN is one of the most powerful and versatile functions in SQL, allowing you to perform advanced operations on your data tables.&nbsp;This way, it is possible to perform&nbsp;selection, update, insertion and classification operations according to specific rules&nbsp;defined by the user. However, many people still don&#8217;t quite understand how CASE WHEN works or how to [&hellip;]<\/p>\n","protected":false},"author":17,"featured_media":3864,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91],"tags":[],"class_list":["post-3852","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>CASE WHEN SQL: What is it and how to use it? - Copahost<\/title>\n<meta name=\"description\" content=\"Discover how to use CASE WHEN in SQL to perform advanced operations like update, insert and sort on your data tables!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CASE WHEN SQL: What is it and how to use it? - Copahost\" \/>\n<meta property=\"og:description\" content=\"Discover how to use CASE WHEN in SQL to perform advanced operations like update, insert and sort on your data tables!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Copahost\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-11T15:06:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-17T19:35:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1075\" \/>\n\t<meta property=\"og:image:height\" content=\"603\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Schenia T\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Schenia T\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\"},\"author\":{\"name\":\"Schenia T\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f\"},\"headline\":\"CASE WHEN SQL: What is it and how to use it?\",\"datePublished\":\"2023-11-11T15:06:40+00:00\",\"dateModified\":\"2023-11-17T19:35:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\"},\"wordCount\":2014,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png\",\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\",\"url\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\",\"name\":\"CASE WHEN SQL: What is it and how to use it? - Copahost\",\"isPartOf\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png\",\"datePublished\":\"2023-11-11T15:06:40+00:00\",\"dateModified\":\"2023-11-17T19:35:00+00:00\",\"description\":\"Discover how to use CASE WHEN in SQL to perform advanced operations like update, insert and sort on your data tables!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage\",\"url\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png\",\"contentUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png\",\"width\":1075,\"height\":603,\"caption\":\"case when sql\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.copahost.com\/blog\/case-when-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.copahost.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CASE WHEN SQL: What is it and how to use it?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#website\",\"url\":\"https:\/\/www.copahost.com\/blog\/\",\"name\":\"Copahost\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.copahost.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\",\"name\":\"Copahost\",\"url\":\"https:\/\/www.copahost.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png\",\"contentUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png\",\"width\":223,\"height\":40,\"caption\":\"Copahost\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f\",\"name\":\"Schenia T\",\"description\":\"Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!\",\"url\":\"https:\/\/www.copahost.com\/blog\/author\/schenia\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CASE WHEN SQL: What is it and how to use it? - Copahost","description":"Discover how to use CASE WHEN in SQL to perform advanced operations like update, insert and sort on your data tables!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.copahost.com\/blog\/case-when-sql\/","og_locale":"en_US","og_type":"article","og_title":"CASE WHEN SQL: What is it and how to use it? - Copahost","og_description":"Discover how to use CASE WHEN in SQL to perform advanced operations like update, insert and sort on your data tables!","og_url":"https:\/\/www.copahost.com\/blog\/case-when-sql\/","og_site_name":"Copahost","article_published_time":"2023-11-11T15:06:40+00:00","article_modified_time":"2023-11-17T19:35:00+00:00","og_image":[{"width":1075,"height":603,"url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png","type":"image\/png"}],"author":"Schenia T","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Schenia T","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#article","isPartOf":{"@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/"},"author":{"name":"Schenia T","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f"},"headline":"CASE WHEN SQL: What is it and how to use it?","datePublished":"2023-11-11T15:06:40+00:00","dateModified":"2023-11-17T19:35:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/"},"wordCount":2014,"commentCount":0,"publisher":{"@id":"https:\/\/www.copahost.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png","articleSection":["MySQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.copahost.com\/blog\/case-when-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/","url":"https:\/\/www.copahost.com\/blog\/case-when-sql\/","name":"CASE WHEN SQL: What is it and how to use it? - Copahost","isPartOf":{"@id":"https:\/\/www.copahost.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png","datePublished":"2023-11-11T15:06:40+00:00","dateModified":"2023-11-17T19:35:00+00:00","description":"Discover how to use CASE WHEN in SQL to perform advanced operations like update, insert and sort on your data tables!","breadcrumb":{"@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.copahost.com\/blog\/case-when-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#primaryimage","url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png","contentUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/11\/CASE-WHEN-SQL.png","width":1075,"height":603,"caption":"case when sql"},{"@type":"BreadcrumbList","@id":"https:\/\/www.copahost.com\/blog\/case-when-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.copahost.com\/blog\/"},{"@type":"ListItem","position":2,"name":"CASE WHEN SQL: What is it and how to use it?"}]},{"@type":"WebSite","@id":"https:\/\/www.copahost.com\/blog\/#website","url":"https:\/\/www.copahost.com\/blog\/","name":"Copahost","description":"","publisher":{"@id":"https:\/\/www.copahost.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.copahost.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.copahost.com\/blog\/#organization","name":"Copahost","url":"https:\/\/www.copahost.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png","contentUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png","width":223,"height":40,"caption":"Copahost"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f","name":"Schenia T","description":"Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!","url":"https:\/\/www.copahost.com\/blog\/author\/schenia\/"}]}},"_links":{"self":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3852","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/comments?post=3852"}],"version-history":[{"count":7,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3852\/revisions"}],"predecessor-version":[{"id":3891,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3852\/revisions\/3891"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/media\/3864"}],"wp:attachment":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/media?parent=3852"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/categories?post=3852"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/tags?post=3852"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}