Re: SQL help needed

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 28 Sep 1998 17:14:40 GMT
Message-ID: <3610c334.18184808@dcsun4.us.oracle.com>

On Mon, 28 Sep 1998 16:09:49 +0100, "AJ Benn" <a.j.benn_at_x400.icl.co.uk> 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 ?
>

Try:

SQL> l
  1 select
  2 job,
  3 sum( decode( sign(1000-sal), 1, 1, null ) ) "<1000",   4 sum( decode( sign(1000-sal), 1, null, -1,

  5                 decode( sign(3000-sal), 1, 1, 0, 1, null ), 1 ))

"1000-3000",
  6 sum( decode( sign(3000-sal), 1, null, 0, null, 1 ) ) ">3000"   7 from scott.emp
  8* group by job
SQL> / JOB <1000 1000-3000 >3000 --------- ---------- ---------- ----------

ANALYST                       2
CLERK              2          2
MANAGER                       3
PRESIDENT                                1
SALESMAN                      4

chris.

>Many thanks
>Andy
>
>(Sorry if the table isn't lined up very well)
>
Received on Mon Sep 28 1998 - 12:14:40 CDT