Decode function for MySQL

Question

I've this below sql:

SELECT 
    DATE_FORMAT(date_created, '%Y-%m-%d')   AS date_
    , IF(STATUS=1,'Success','Failed')       AS PROCESSED_STATUS
    ,COUNT(1)
FROM 
    x
WHERE 
    DATE_FORMAT(date_created, '%Y-%m-%d') BETWEEN  '2011-01-01' AND '2015-06-04'
GROUP BY 
    DATE_FORMAT(date_created, '%Y-%m-%d')
    , STATUS
ORDER BY 
    DATE_FORMAT(date_created, '%Y-%m-%d') DESC;

actually the format will shown like below

Date_       PROCESSED_STATUS    Count(1)
2015-05-15  Failed              1
2015-05-13  Failed              691
2015-05-13  Success             68
2012-05-19  Failed              346
2012-05-19  Success             28
2012-05-18  Failed              184
2012-05-18  Success             18
2012-05-17  Failed              176
2012-05-17  Success             9
2012-05-16  Failed              425
2012-05-16  Success             49
2012-03-13  Failed              1
2012-02-23  Success             193

but I need this format:

Date_   Failed    Success

and inside on the data will shown the date and the count of failed and success.

Usually on oracle, I use the decode function for this, but I don't know for mysql. please help :)

Answer 1

Use conditional aggregation:

SELECT DATE_FORMAT(date_created, '%Y-%m-%d') AS date_, 
       SUM(STATUS = 1) as Success,
       SUM(STATUS <> 1 OR STATUS IS NULL) as Failed
FROM x
WHERE DATE_FORMAT(date_created, '%Y-%m-%d') BETWEEN  '2011-01-01' AND '2015-06-04'
GROUP BY DATE_FORMAT(date_created, '%Y-%m-%d')
ORDER BY DATE_FORMAT(date_created, '%Y-%m-%d') DESC;

Answer 2

Move the condition into the COUNT/SUM:

SELECT DATE_FORMAT(date_created, '%Y-%m-%d') AS date_, 
   SUM(CASE WHEN Status=1 THEN 0 ELSE 1 END AS Failed,
   SUM(CASE WHEN Status=1 THEN 1 ELSE 0 END AS Success
FROM x
WHERE DATE_FORMAT(date_created, '%Y-%m-%d') BETWEEN  '2011-01-01' AND '2015-06-04'
GROUP BY DATE_FORMAT(date_created, '%Y-%m-%d')
ORDER BY DATE_FORMAT(date_created, '%Y-%m-%d') DESC;

I used Standard SQL CASE instead of proprietary IF/DECODE because it's portable