CASE statements are an important aspect of SQL programming. They allow you to control the flow of your query execution and make decisions based on specific conditions. CASE statements can be used in various parts of an SQL query, including (1) the SELECT statement, (2) the WHERE clause, (3) the HAVING Clause, and (4) within aggregate functions like SUM, AVG, and COUNT.
A basic CASE statement in SQL has the following syntax:
CASE
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
The WHEN clause specifies a condition that will be evaluated. If the condition is met, the result following the THEN clause will be returned. The ELSE clause is optional and is used to specify a result if none of the conditions are met.
CASE Statement in a SELECT Clause
Here is an example of how you can use a CASE statement in an SQL SELECT statement:
SELECT name, age,
CASE
WHEN age <= 18 THEN 'Minor'
WHEN age >= 18 AND age <= 25 THEN 'Young Adult'
ELSE 'Adult'
END as age_group
FROM customers;
In this example, we are using the CASE statement to categorize customers based on their age. If a customer’s age is less than or equal to 18, they will be classified as a “Minor”. If a customer’s age is greater than or equal to 18 and less than or equal to 25, they will be classified as a “Young Adult”. If a customer’s age does not meet either of these conditions, they will be classified as an “Adult”.
CASE Statement in a SELECT Clause, Within An Aggregate Function
You can also use a CASE statement within an aggregate function like SUM, AVG, or COUNT. For example, you can use a CASE statement to determine how many managers are in your payroll:
SELECT job_title,
SUM(CASE
WHEN job_title = 'Manager' THEN 1 ELSE 0
END as average_salary
FROM employees
In this example, the CASE statement is used first to assign 1 to employees who are managers and assign a 0 to those who are not. The SUM function is then used to find the numbers of employees who are managers.
CASE Statement in a WHERE Clause
CASE statements can also be used in the WHERE clause of an SQL query. For example, you can use a CASE statement to retrieve records based on certain conditions:
SELECT *
FROM orders
WHERE status = CASE
WHEN total >= 100 THEN 'Completed'
ELSE 'Pending'
END;
In this example, the CASE statement is used in the WHERE clause to retrieve only orders with a status of “Completed” if the order total is greater than or equal to $100. If the order total is less than $100, the status will be “Pending”.
CASE Statement in a HAVING Clause
In this example, the CASE statement is used within the AVG function to only include salary values for employees with the job title “Manager”. The HAVING clause is used to filter the result to only show average salaries that are greater than or equal to $50,000. You can also use a CASE statement within an aggregate function like SUM, AVG, or COUNT.
For example, you can use a CASE statement to determine the average salary based on job titles:
SELECT job_title, AVG(salary) as average_salary
FROM employees
GROUP BY job_title
HAVING AVG(CASE
WHEN job_title = 'Manager' THEN salary
ELSE 0
END) >= 50000;
The `Searched` Format of CASE Statements
In addition to the basic syntax, CASE statements can also be written using the SEARCHED format. The SEARCHED format allows you to include multiple conditions and results within a single WHEN clause. The syntax for the SEARCHED format is the same as thebasic format.
Here is an example of how you can use the SEARCHED format in a SELECT statement:
SELECT name, age, salary,
CASE
WHEN salary >= 50000 THEN 'High Earner'
WHEN salary >= 30000 AND salary < 50000 THEN 'Average Earner'
ELSE 'Low Earner'
END as salary_group,
CASE
WHEN age <= 18 THEN ' Minor'
WHEN age >= 18 AND age <= 25 THEN 'Young Adult'
ELSE 'Adult'
END as age_group,
CASE
WHEN salary >= 50000 AND age >= 18 AND age <= 25 THEN 'High Earning Young Adult'
WHEN salary >= 50000 AND age > 25 THEN 'High Earning Adult'
WHEN salary >= 30000 AND salary < 50000 AND age >= 18 AND age <= 25 THEN 'Average Earning Young Adult'
WHEN salary >= 30000 AND salary < 50000 AND age > 25 THEN 'Average Earning Adult'
ELSE 'Low Earning Individual'
END as earning_category
FROM customers;
In this example, the SEARCHED format allows you to specify multiple conditions and results within a single WHEN clause. The ELSE clause is optional and is used to specify a result if none of the conditions are met.
Conclusion
CASE statements are a powerful tool in SQL programming that allows you to control the flow of your query execution and make decisions based on specific conditions. They can be used as part of a SQL query, including a SELECT statement, GROUP BY and HAVING clauses
Leave a Reply