1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Case Expression

Discussion in 'SQL PL/SQL' started by praveenkumarglory, Sep 20, 2011.

  1. praveenkumarglory

    praveenkumarglory Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    >select count(*) from emp where commission >20;
    >select count(*) from emp where commission <50;
    >select count(*) from emp where commission between 20 and 50;

    Instead of using the above 3 separate queries, how to get those three count values in a single query using CASE Expression..

    kindly share your thoughts..

    Thanks,
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    CASE doesn't work that way and isn't designed for such tasks. You want UNION, I think:

    Code (SQL):
    SQL> SELECT COUNT(*) FROM emp WHERE commission >20
      2  UNION
      3  SELECT COUNT(*) FROM emp WHERE commission <50
      4  UNION
      5  SELECT COUNT(*) FROM emp WHERE commission BETWEEN 20 AND 50;
      COUNT(*)
    ----------
             4
             5
            13
    SQL>
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There are some ways of doing it with DECODE.

    Code (SQL):
     
    SQL> SELECT COUNT(*) FROM EMP WHERE SAL  >1000;

      COUNT(*)
    ----------
            12

    SQL> SELECT COUNT(*) FROM EMP WHERE SAL <2000;

      COUNT(*)
    ----------
             8

    SQL> SELECT COUNT(*) FROM EMP WHERE SAL BETWEEN 1000 AND 2000;

      COUNT(*)
    ----------
             6

    SQL> SELECT COUNT(CASE WHEN SAL>1000 THEN 1 END) SAL_LT_1K ,
      2         COUNT(CASE WHEN SAL<2000 THEN 1 END) SAL_GT_2K ,
      3         COUNT( CASE WHEN SAL BETWEEN 1000 AND 2000 THEN 1 END) SAL_BT_1K_2K
      4  FROM EMP;

     SAL_LT_1K  SAL_GT_2K SAL_BT_1K_2K
    ---------- ---------- ------------
            12          8            6

    SQL> SELECT COUNT(DECODE( SIGN( SAL -1000 ) , 1, 1)) SAL_LT_1K,
      2         COUNT( DECODE( SIGN( 2000 - SAL ) , 1, 1)) SAL_GT_2K,
      3         COUNT( DECODE( SIGN( SAL -1000 ),1,DECODE( SIGN( 2000 - SAL ) , 1, 1) )) SAL_BT_1K_2K
      4  FROM EMP;

     SAL_LT_1K  SAL_GT_2K SAL_BT_1K_2K
    ---------- ---------- ------------
            12          8            6

    SQL>
     
     
  4. praveenkumarglory

    praveenkumarglory Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Raj and David