MySQL conditional expression CASE
MySQL conditional expression CASE
MySQL CASE expression is a conditional expression that allows you to construct conditions inside a query such as SELECT or WHERE clause.
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
ELSE result END
The
CASE expression returns the result such as result_1, result_2, etc., if the condition is true. If all conditions are false, then the result in the ELSE part is returned. If the ELSE part is omitted, the CASE expression returns NULLselect agent,
sum((case when (event = ‘COMPLETECALLER’) then 1 else 0 end)) AS `COMPLETECALLER`,
sum((case when (event = ‘COMPLETEAGENT’) then 1 else 0 end)) AS `COMPLETEAGENT`
time >=curdate() group by agent
sum((case when (event = ‘COMPLETEAGENT’) then 1 else 0 end)) AS `COMPLETEAGENT`
time >=curdate() group by agent
Please see the highlited part again
sum((case when (event = ‘COMPLETECALLER’) then 1 else 0 end)) AS `COMPLETECALLER`,
sum((case when (event = ‘COMPLETEAGENT’) then 1 else 0 end)) AS `COMPLETEAGENT`
sum((case when (event = ‘COMPLETEAGENT’) then 1 else 0 end)) AS `COMPLETEAGENT`
Here its sum of the value of COMPLETECALLER and COMPLETEAGENT are calculated with the help of case conditional expression.
You can also look at the another example here :
SELECT
SUM(CASE
WHEN status = ‘Shipped’ THEN 1
ELSE 0
END) AS ‘Shipped’,
SUM(CASE
WHEN status = ‘On Hold’ THEN 1
ELSE 0
END) AS ‘On Hold’,
SUM(CASE
WHEN status = ‘In Process’ THEN 1
ELSE 0
END) AS ‘In Process’,
SUM(CASE
WHEN status = ‘Resolved’ THEN 1
ELSE 0
END) AS ‘Resolved’,
SUM(CASE
WHEN status = ‘Cancelled’ THEN 1
ELSE 0
END) AS ‘Cancelled’,
SUM(CASE
WHEN status = ‘Disputed’ THEN 1
ELSE 0
END) AS ‘Disputed’,
COUNT(*) AS Total
FROM
orders;
