CODEWITHSUNDEEP
mysql
ace
ace

Reputation: 6825

Nested CASE in MySQL Query

I want to count the total numbers of orders where task is 1 to 3. Then if entry1 is already counted I need to check if the status is in-progress (all status which are not completed). I need to use a nested CASE here I don't want to include it in my WHERE clause because I will join more tables later.

This is my sample table:

ID  +    orderid  +  task    +  status
1   |    1        |  1       |  Completed
2   |    2        |  1       |  Saved
3   |    3        |  1       |  Saved
4   |    1        |  2       |  Completed
5   |    1        |  3       |  Completed

As you can see in task field which have entry1 the total of in-progress is 2 and completed is 1. When I execute my query I get results of 2 when I used entry1 and the status is not Saved but I also get the result of 2 when removing the NOT. But when I used ('COMPLETED') I get the right result. So what's the problem with ('SAVED') in my code. I hope I did not mistype anything in my question.

This is my sample query:

SELECT 
    COUNT(
        CASE task
            WHEN 1 THEN task ELSE NULL
        END
        AND
        CASE `status`
            WHEN NOT UPPER('SAVED') THEN `status` ELSE NULL
        END
        ) e1_inprogress
   FROM tableName;

For now I just use only task 1 but in my real code I need also to include all task and all status in COUNT. I think this is simple maybe I missing something then please correct me. If you have another way please let met know.

Upvotes: 0

Views: 2521

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

Use this

SELECT 
    COUNT(
        CASE WHEN task = 1 AND Upper(`status`) = 'SAVED' THEN 1 END
        ) e1_inprogress
   FROM tableName;

Why it didn't work?

WHEN NOT UPPER('SAVED')

NOT UPPER('SAVED') becomes NOT (TRUE) becomes FALSE, so you are comparing CASE Status against the value FALSE.

Your query could have been

CASE Upper(`Status`)
WHEN 'SAVED' THEN ... ELSE ...

(nb: swap the code for THEN and ELSE)

Upvotes: 1

Related Questions