I want to use Case When with AND condition and it is not calculating the sum properly.
For example:
SELECT DATE(`SubmitDate`),
SUM(CASE status WHEN 'New' AND `Type` = 'consumer' THEN 1 ELSE 0 END) as new_consumer,
SUM(CASE status WHEN 'New' AND `Type` = 'business' THEN 1 ELSE 0 END) as new_business
FROM report
WHERE `source` = 'net'
group by DATE(`SubmitDate`) Order by `SubmitDate` DESC
2 Answers
You need to use CASE WHEN [Condition] THEN... rather than a simple case expression:
SELECT DATE(`SubmitDate`),
SUM(CASE WHEN status = 'New' AND `Type` = 'consumer' THEN 1 ELSE 0 END) as new_consumer,
SUM(CASE WHEN status = 'New' AND `Type` = 'business' THEN 1 ELSE 0 END) as new_business
FROM report
WHERE `source` = 'net'
group by DATE(`SubmitDate`) Order by `SubmitDate` DESC
You should write
CASE WHEN status='New' AND `Type` = 'consumer' THEN 1 ELSE 0 END
Check the syntax of CASE WHEN
