MySQL is one of the most popular relational database management systems, and is widely used for web applications. One of the most powerful features of MySQL is the CASE WHEN statement. If you are new to MySQL or are looking to improve your skills, this article will show you how to use MySQL CASE WHEN.
What is MySQL CASE WHEN?
MySQL CASE WHEN is a conditional statement that allows you to execute different code blocks based on different conditions. It is similar to the switch statement in other programming languages. The syntax of the MySQL CASE WHEN statement is as follows:
“`
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
WHEN condition_n THEN result_n
ELSE default_result
END
“`
The CASE statement begins with the keyword CASE, followed by one or more WHEN clauses, and ends with the keyword END. Each WHEN clause contains a condition and a result. If the condition is true, the corresponding result is returned. If none of the conditions are true, the default_result is returned.
Using MySQL CASE WHEN
Let’s say you have a table called customers that contains information about your customers. You want to create a report that shows the number of customers in each age group. You can use the MySQL CASE WHEN statement to group customers by age:
“`
SELECT
CASE
WHEN age < 18 THEN ‘Under 18′
WHEN age BETWEEN 18 AND 24 THEN ’18-24′
WHEN age BETWEEN 25 AND 34 THEN ’25-34′
WHEN age BETWEEN 35 AND 44 THEN ’35-44′
WHEN age BETWEEN 45 AND 54 THEN ’45-54′
ELSE ’55 and over’
END AS age_group,
COUNT(*) AS total_customers
FROM customers
GROUP BY age_group;
“`
In this example, the CASE statement checks the age of each customer and assigns them to an age group based on the conditions in the WHEN clauses. The result is a report that shows the number of customers in each age group.
You can also use the MySQL CASE WHEN statement with aggregate functions like SUM, AVG, MIN, and MAX. For example, let’s say you have a table called sales that contains information about your sales. You want to create a report that shows the total sales for each product category. You can use the MySQL CASE WHEN statement to group sales by product category:
“`
SELECT
CASE
WHEN product_category = ‘Electronics’ THEN ‘Electronics’
WHEN product_category = ‘Clothing’ THEN ‘Clothing’
WHEN product_category = ‘Books’ THEN ‘Books’
ELSE ‘Other’
END AS product_category,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_category;
“`
In this example, the CASE statement checks the product category of each sale and assigns them to a category based on the conditions in the WHEN clauses. The result is a report that shows the total sales for each product category.
You can also use the MySQL CASE WHEN statement to update records in a table. For example, let’s say you have a table called employees that contains information about your employees. You want to update the salary of employees based on their job title. You can use the MySQL CASE WHEN statement to update the salary of employees based on their job title:
“`
UPDATE employees
SET salary = CASE
WHEN job_title = ‘Manager’ THEN salary * 1.1
WHEN job_title = ‘Supervisor’ THEN salary * 1.05
ELSE salary
END;
“`
In this example, the CASE statement checks the job title of each employee and updates their salary based on the conditions in the WHEN clauses. Employees with the job title of Manager will have their salary increased by 10%, employees with the job title of Supervisor will have their salary increased by 5%, and all other employees will have their salary remain the same.
The MySQL CASE WHEN statement is a powerful tool that allows you to execute different code blocks based on different conditions. It can be used to group data, perform calculations, and update records in a table. If you are new to MySQL or are looking to improve your skills, the MySQL CASE WHEN statement is a good place to start. With a little practice, you can use it to make your MySQL queries more powerful and efficient.