Re: SQL help needed

From: Chrysalis <cellis_at_clubi.ie>
Date: Thu, 01 Oct 1998 22:24:15 +0100
Message-ID: <cellis-ya02408000R0110982224150001@news.clubi.ie>

In article <360fa584.0_at_145.227.194.253>, "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 ?
>
> Many thanks
> Andy
>
> (Sorry if the table isn't lined up very well)

You mau find the following, using the greatest/least functions, a neater and possibly faster (1) alternative to the perfectly good solutions posted so far:

select job

      ,sum(decode(least(sal,999.99),sal,1,null)) "<1000" -- (2)
      ,sum(decode(greatest(1000,least(sal,3000)),sal,1,null) "1000-3000"
      ,sum(decode(greatest(sal,3000.01),sal,1,null) ">3000" -- (3)

from emp
group by job;

Notes:

(1) Evaluation of the SQL/PLSQL sign() function is, for some reason, quite slow.
(2) Sal <= 999.99
(3) Sal >= 3000.01

HTH
Chrysalis
--
FABRICATE DIEM PVNC
("To Protect and to Serve")
Motto of the Night Watch
Terry Pratchett - "Guards, Guards" Received on Thu Oct 01 1998 - 16:24:15 CDT