Re: SQL help needed

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Sep 1998 16:06:18 GMT
Message-ID: <3615b3f2.13538617@192.86.155.100>

A copy of this was sent to "AJ Benn" <a.j.benn_at_x400.icl.co.uk> (if that email address didn't require changing) On Mon, 28 Sep 1998 16:09:49 +0100, you wrote:

>Using the Oracle example table 'EMP', I want to produce a query that will
>show me the number of employees whose Salary falls within a particular range
>(i.e less than 1000, between 1000 and 3000, and then greater than 3000), and
>then grouped by Job. The output I want is as follows:-
>
>Job $ <1000 $ 1000-3000 $ 3000+
>======= ==== ======== =====
>Clerk 2 2
>Salesman 4
>Manager 3
>Analyst 2
>President
>1
>
>Anyone offer any help on this knotty little problem ?
>
>Many thanks
>Andy
>
>(Sorry if the table isn't lined up very well)
>

select job,

       sum( decode( sign(1000-sal), 1, sal, 0 ) ) "<1000",
       sum( decode( sign(3000-sal), 1, decode(sign(1000-sal),-1,sal,0, sal, 0),
0 ) ) "1000 to <3000",
       sum( decode( sign(3000-sal),-1, sal, 0, sal, 0 ) ) "3000 and up"

from emp
group by job
/

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 Mon Sep 28 1998 - 11:06:18 CDT