CODEWITHSUNDEEP
mysqlsqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Case When with AND?

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

Upvotes: 2

Views: 26871

Answers (2)

GarethD
GarethD

Reputation: 69789

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

Upvotes: 6

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131591

You should write

CASE  WHEN status='New' AND `Type` = 'consumer' THEN 1 ELSE 0 END

Check the syntax of CASE WHEN

Upvotes: 3

Related Questions