We will see the use of case statement in sql with the help of an example
If we have cupcake sales data, and if the sales amount > 10000, we want to
set “Big Order” and if the sales order <10000, I want to set “Small Order”.
Example
So the logic will be if sales order >10000, then it will
give “ Big Order” else “Small Order”
Basically, a case statement in SQL allows us to create
conditional expressions similar to an IF-THEN-ELSE statement.
Another feature of the SQL case statement is it adds a
column with values based on the condition.
The syntax for case statement is
SELECT column1, column2,... ,
CASE
WHEN condition THEN
result
END AS alias_name
FROM table;
The syntax of CASE always starts with the CASE keyword and
ends with the END keyword followed by a column name alias.
Let's see this with an example
Query for Table cupcake
CREATE TABLE cupcake (
product_id INT
PRIMARY KEY,
flavor
VARCHAR(50),
sale_amount INT
);
INSERT INTO cupcake (product_id, flavor, sale_amount)
VALUES
(1, 'Vanilla',
10000),
(2, 'Chocolate',
20000),
(3, 'Strawberry',
2750),
(4, 'Red Velvet',
3500),
(5, 'Lemon',
2250),
(6, 'Blueberry',
3250),
(7, 'Caramel',
4000),
(8, 'Pumpkin
Spice', 3750),
(9, 'Mocha',
4250),
(10, 'Raspberry',
2900);
select * from cupcake
CUPCAKE SALES DATA
Query with case statement
select *,
case
when sale_amount >=10000 then 'Big Order'
when sale_amount <10000 then 'Small Order'
end as order_volume
from cupcake;
Result
Here is the result. We can see as per our condition wherever
the sales amount > 10000, we are getting “Big Order” and if the sales order <10000,
we are getting “small order”., in a new column.
Important points to take care of while using a case statement are
- CASE statement always starts with the CASE
keyword
- Ends with END keyword
- The END keyword is followed by a column name alias.
- A case statement adds a column based on the given condition
No comments:
Post a Comment