Re: A difficult SQL problem... please help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/06/24
Message-ID: <35960365.3604643_at_192.86.155.100>#1/1

A copy of this was sent to Lee Ka Wo <leekawo_at_netvigator.com> (if that email address didn't require changing) On Wed, 24 Jun 1998 21:10:27 +0800, you wrote:

>Dear Experts,
>
>I get a problem....
>
>If I have a tables full of salary., I need to group them into several
>salary "ranges" like this:
>
> <1000 >=1000 to < 2000 >=2000 to <=2500 >2500
>
>Male 100 200 120 50
>Famale 50 222 30 65
>
>
>
>What should I do ??
>
>I wonder whether "decode" and "union" can help me ??
>
>e.g.
>
>Select count(*) , decode (??????) from people group by sex
>
>
>Ken

On possible solution looks like:

SQL> select deptno, sal,

  2                decode( sign(sal-1000), -1, '<1000',
  3                   decode(sign(sal-2000), -1, '>=1000 to <2000',
  4                     decode(sign(sal-2500), -1, '>=2000 to <2500', '>=2500')
  5                         )
  6                      ) converted_sal
  7            from emp

  8 /

    DEPTNO SAL CONVERTED_SAL
---------- ---------- ---------------

        10     2572.5 >=2500
        10       5500 >=2500
        10        920 <1000
        20    3123.75 >=2500
        30       1600 >=1000 to <2000
        30       2000 >=2000 to <2500
        20       3450 >=2500

7 rows selected.

that gets the salary ranges rolled up for us, we can then use an inline view as such to pivot and sum:

SQL> select deptno,

  2         sum(decode(sal, '<1000'          , 1, 0 )) "<1000",
  3         sum(decode(sal, '>=1000 to <2000', 1, 0 )) ">=1000 to <2000",
  4         sum(decode(sal, '>=2000 to <2500', 1, 0 )) ">=2000 to <2500",
  5         sum(decode(sal, '>=2500'         , 1, 0 )) ">=2500"
  6  from ( select deptno,
  7                decode( sign(sal-1000), -1, '<1000',
  8                   decode(sign(sal-2000), -1, '>=1000 to <2000',
  9                     decode(sign(sal-2500), -1, '>=2000 to <2500', '>=2500')
 10                         )
 11                      ) sal
 12            from emp )

 13 group by deptno
 14 /

    DEPTNO <1000 >=1000 to <2000 >=2000 to <2500 >=2500 ---------- ---------- --------------- --------------- ----------

        10          1               0               0          2
        20          0               0               0          2
        30          0               1               1          0

You would change deptno to gender. My example works on scott.emp...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jun 24 1998 - 00:00:00 CEST