>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,

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>

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>