MySQL conditional expression CASE

5 (100%) 2 votes

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_1result_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 NULL
select 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
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` 
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;
For any query or issue, feel free to discuss on http://discuss.eduguru.in
%d bloggers like this: