Re: SQL help needed

From: Nuno Guerreiro <nuno-v-guerreiro_at_invalid.telecom.pt>
Date: Mon, 28 Sep 1998 16:56:26 GMT
Message-ID: <3617b708.1230932677@news.telecom.pt>

select	job,
	sum( decode(trunc(salary/1000),
			0,1,
			0)
		) "$<1000",
	sum( decode(salary,
			1000,1,
			3000,1,
			decode(trunc(salary/1000)-trunc(salary/3000),
				1,1,
				0)
			)
		) "$1000-3000",
	sum( decode(salary,
			3000,0,
			decode(trunc(salary/3000),
				1,1,
				0)
			)
		 ) "$>3000"
from	emp

group by job;

I haven't done intensive testing, though. Use it carefully.

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 ?
>
>Many thanks
>Andy
>
>(Sorry if the table isn't lined up very well)
>
>

Nuno Guerreiro

As I don't normally appreciate unsolicited commercial e-mail (widely known as SPAM), I encoded my e-mail address. If you want to reply by e-mail, please remove the text added to fool spam software

"The art of arts, the glory of expression and the sunshine that lights the light of letters  is simplicity"

                                                        Walt Whitman Received on Mon Sep 28 1998 - 11:56:26 CDT