-
Hi friends..
I have to execute one query for following requirement but unable to write query can anybody guide...
Table format
---------------
empno
deptno
salary
required format
------------------
Deptno sal[0-5000] sal[5000-10000] sal[10000-20000]
10 20 30 15
20 40 19 10
How to get above information through the above table.
thanks in advance...
sandeep
-
Originally posted by ctcyl
10 20 30 15
20 40 19 10
I have no idea what these numbers are supposed to represent, so that makes it a little difficult to write the actual query for you. Basically, if you are using >= 8.1.6.2, then you want to look at the new CASE statement. If not, then you either need a lot of DECODEs, a formula to calculate your buckets, or a separate table containing the buckets (ranges).
HTH
- Chris
-
select dept_no,
sum(decode(sign(salary-5001),-1,1)) "0-5000",
sum(decode(sign(salary-5000),1,
decode(sign(salary-10001),-1,1))) "5001-10000",
sum(decode(sign(salary-10000),1,
decode(sign(salary-20001),-1,1))) "10001-20000"
from your_table_name_here
-
Well, just as a curiosity, you could get the desired output here even without using DECODE or CASE function. All you need is a litle gymnastics with the SIGN function:
select dept_no,
sum(sign(sign(salary)+sign(5000-salary))) "0-5000",
sum(sign(sign(salary-5001)+sign(10000-salary))) "5001-10000",
sum(sign(sign(salary-10001)+sign(20000-salary))) "10001-20000",
from your_table_name_here
group by deptno;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Jurij - you've been reading Celko too much, I think
He had an entire 'sub-language' developed to handle all these types of things before CASE became part of the SQL standard. (for others - DECODE is the poor relative of CASE). Very scary stuff
Point to ctcyl: These queries essentially do COUNTs by bucket. Is that what you wanted? Or did you want something else, like the sum of the salaries within each bucket. I'm still not sure what you were asking for.
- Chris
-
Originally posted by chrisrlong
Jurij - you've been reading Celko too much, I think
He had an entire 'sub-language' developed to handle all these types of things before CASE became part of the SQL standard. (for others - DECODE is the poor relative of CASE). Very scary stuff
Chris,
Tthe fact is I havent read any of Celco's articles for at least three years now . Not that I don't like him, my current employer is simply not subscribed to any magazine with his regular columns.
But it looks like I've had the same "obsession" as he had in the past, inventing various "sub-language constructs" for implementing IF-THEN-ELSE SQL logic that is now soooo easy to implement with CASE.
In fact I discovered that you don't even need DECODE (or CASE) in this example purely by incident while I was writing a reply to cheland's solution, where I imediatelly spoted he is using a couple of DECODES too many. Typical and unnecessary using of nested DECODEs that most Oracle users do, because it is not documented anywhere in the documentation how to code IF-ELSIF without nesting the DECODEs.
For example, his "BETWEEN 5001 AND 10000" implementation with nested DECODEs:
SUM(DECODE(SIGN(salary-5000), 1, DECODE(SIGN(salary-10001), -1, 1))) "5001-10000"
could be rewritten into a more efficient, single DECODE construct:
SUM(DECODE(1, SIGN(5001-salary), NULL,SIGN(10001-salary), 1)) "5001-10000"
Of course, I would not want to "decipher" the meaning of any of this various sollution implementations (nested decodes, single decode, sign-sign-sign,....) after a month or so - it would probably caused me a terrible headacke. While on the other hand, with CASE it would only take a glimpse to understand it....
[Edited by jmodic on 05-08-2001 at 06:31 PM]
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|