user3408779 user3408779 - 1 month ago 6
MySQL Question

Using Where clause in case mysql

Below is my query and i am getting total number of related status values correctly. But i need those values in a specific time range. For getting total values below is my query.

SELECT
SUM(CASE
WHEN `status` = 'saved_for_review' THEN 1
ELSE 0
END) AS SavedCases,
SUM(CASE
WHEN STATUS = 'send_to_warehouse' THEN 1
ELSE 0
END) AS SendtoWareshouse,
SUM(CASE
WHEN STATUS = 'send_to_cep' THEN 1
ELSE 0
END) AS SendtoCEP,
SUM(CASE
WHEN STATUS = 'Successful' THEN 1
ELSE 0
END) AS Closed
FROM table_details


But i need values in specific time range like

where date(created_on) between '20160701' and '20160901'


How can i add this where clause to the above query. Any help would be greatly appreciated.

Answer

First of all just writing SUM(status='saved_for_review') SavedCases works and is much simpler and faster. As for the WHERE clause, you just add it after the FROM clause.

SELECT 
    SUM(status = 'saved_for_review') SavedCases,
    SUM(status = 'send_to_warehouse') SendtoWarehouse,
    SUM(status = 'send_to_cep') SendtoCEP,
    SUM(status = 'Successful') Closed 
FROM table_details
WHERE DATE(created_on) BETWEEN '20160701' AND '20160901'
Comments