xxxxxxxxxx
SELECT
season,
ROUND(AVG(
CASE -- Start CASE
WHEN hometeam_id = 8455 AND home_goal > away_goal THEN 1
WHEN hometeam_id = 8455 AND home_goal < away_goal THEN 0
ELSE NULL
END -- End CASE
), 2) AS pct_homewins,
FROM table_name
WHERE hometeam_id = 8455 OR awayteam_id = 8455 -- ADVISED : make sure to filter, else the new column will contain many NULLS with all data
GROUP BY season;
xxxxxxxxxx
-- NOTE: this is for SQL-Oracle specifically
/*
NB: Please like Mingles444 post, I derived this from him/her
*/
-- syntax: (Retrieved from grepper:Mingles444)
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
-- example:
SELECT
CASE
WHEN (1+6 = 6) THEN 'A'
WHEN (1+6 = 7) THEN 'B'
WHEN (1+6 = 8) THEN 'C'
ELSE 'D'
END
FROM DUAL;
-- OUTPUT: B
xxxxxxxxxx
-- syntaxis: (BY Junior Cercado)
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
xxxxxxxxxx
Change query output depending on conditions.
Example: Returns users and their subscriptions, along with a new column
called activity_levels that makes a judgement based on the number of
subscriptions.
SELECT first_name, surname, subscriptions
CASE WHEN subscriptions > 10 THEN 'Very active'
WHEN Quantity BETWEEN 3 AND 10 THEN 'Active'
ELSE 'Inactive'
END AS activity_levels
FROM users;
xxxxxxxxxx
CASE
WHEN SUBSTRING(applications.pinfl, 1,1) IN ('3','5','7') THEN 'Erkak'
WHEN SUBSTRING(applications.pinfl, 1,1) IN ('2','4','6') THEN 'Ayol'
ELSE 'Topilmadi'
END as gender
xxxxxxxxxx
SELECT order_id, customer_id,
CASE
WHEN amount >= 400 THEN (amount - amount * 10/100)
END AS offer_price
FROM Orders;
xxxxxxxxxx
SELECT customer_id, first_name,
CASE
WHEN age >= 18 THEN 'Allowed'
END AS can_vote
FROM Customers;
xxxxxxxxxx
SELECT column_name,
CASE
WHEN boolean_condition THEN 'Result_1'
WHEN boolean_condition THEN 'Result_2'
ELSE 'Result_3'
END AS result_column_name
FROM table_name_1
LEFT JOIN table_name_2 ON
CASE
WHEN boolean_condition THEN 'Boolean_Result_1'
WHEN boolean_condition THEN 'Boolean_Result_2'
ELSE 'Boolean_Result_3'
END;
xxxxxxxxxx
Case Statement basically
Like IF - THEN - ELSE statement.
The CASE statement goes through conditions
and returns a value when the
first condition is met and
once a condition is true,
it will stop reading and return the result.
If no conditions are true,
it returns the value in the ELSE clause.
If there is no ELSE part and
no conditions are true, it returns NULL.
FOR EXAMPLE =
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
-- example:
SELECT
CASE
WHEN (1+6 = 6) THEN 'A'
WHEN (1+6 = 7) THEN 'B'
WHEN (1+6 = 8) THEN 'C'
ELSE 'D'
END
FROM DUAL;
Result would be 'B' since it is the first
correct answer