CODEWITHSUNDEEP
mysqlcase
Vedran Mile Tomljanovic
Vedran Mile Tomljanovic

Reputation: 73

MySQL count with case return wrong count

I have following problem:

I have mysql table with: 

continent,  
country,  
city,  
zip,  
street,  
status (int),  
reason (varchar, can be empty or some text),  
... (some other columns, not required for query)

I want to count occurence of each status by country,city,zip,street but with mapping of status int to text:

SELECT country,city,zip,street, 
   CASE WHEN (status = '2' AND reason <> '') THEN "BLOCKED" 
        WHEN (status = '2' AND reason='') THEN "DISPUTED"  
        WHEN status = '3' THEN 'EXPIRED' 
        WHEN status = '1' THEN 'ACTIVE' ELSE 'UNKNOWN' 
        END as status, 
    count(*) AS count
FROM people
where continent='Europe'
GROUP BY country,city,zip,street,status

I think that problem is in GROUP BY but not sure - it don't return correct data.

SQLFiddle (Check Paris/Street2 - it shows 3 DISPUTED and should show 1 BLOCKED and 2 DISPUTED)

Thanks for opinions.

Upvotes: 0

Views: 81

Answers (1)

symcbean
symcbean

Reputation: 48357

I think EngineerCoder meant to write:

select country,city,zip,street, status, count(*) 
from
(
SELECT country,city,zip,street, 
   CASE WHEN (status = '2' AND reason <> '') THEN "BLOCKED" 
        WHEN (status = '2' AND reason='') THEN "DISPUTED"  
        WHEN status = '3' THEN 'EXPIRED' 
        WHEN status = '1' THEN 'ACTIVE' ELSE 'UNKNOWN' 
   END as status, 
FROM people
where continent='Europe'
) AS ilv
GROUP BY country,city,zip,street,status

Upvotes: 2

Related Questions