user3209031
user3209031

Reputation: 835

SUM/COUNT of CASE within CASE in MYSQL

Have below Column in table product :

a  |  b  | cat
--------------
2  |  4  |  1
3  |  1  |  1
4  |  4  |  1
2  |  1  |  1
3  |  2  |  1
5  |  3  |  1
8  |  7  |  2
5  |  3  |  2


$sql = "SELECT 
        SUM(CASE 
            WHEN
            cat = 1
            AND
            a > b  
            THEN 1 else 0 end) as prdplus
        FROM product
       ";

in above query i am getting value for sum or count of a greater then b

which is 3>1 , 2>1 , 3>2 , 5>3

hence output of above query will be : 4

prdplus
-------
4

what i need is count value have only +1 in above count of 4

thats with above query now i need +1 value count

3-1 = 2
2-1 = 1  // just explanation purpose , output i need in -> plus1 
3-2 = 1  // just explanation purpose , output i need in  -> plus1
5-3 = 2

hence with above query i need only +1 count which is 2

i am not getting how to write case within case including above query as i need both count to show in my table as below

Output Final :

 prdplus | plus1 
----------------
  4      |  2

Upvotes: 1

Views: 540

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Check This.

        SELECT 
                SUM(CASE 
                    WHEN
                    a > b  
                    THEN 1 else 0 end) as prdplus  
                   , 
                      SUM(CASE WHEN a - b = 1  
                        THEN 1 
                        else 0 
                   end) as plus1 

                FROM 
                (


        select 2 as a  , 4 as b union
        select 3   , 1 union
        select 4   ,  4 union
        select 2   , 1 union
        select 3   , 2 union
        select 5   , 3
                )
         a

Upvotes: 1

juergen d
juergen d

Reputation: 204756

SELECT SUM(CASE WHEN a > b  
                THEN 1 
                else 0 
           end) as prdplus,
       SUM(CASE WHEN a - b = 1  
                THEN 1 
                else 0 
           end) as prdplus1
FROM product

Upvotes: 1

Related Questions