-
Query
Hi,
I have a query like this , I want the count basing on two condition :
With < 50 and >50 .
I have written the query like this for <50 which is working fine , how can I get those value with condition >50
select m.empcode, p.courtaction,b.benchname,(count(*)*b.points)
from main m ,proceedings p ,benchcategory b
WHERE P.MAINCASE.SIDE = M.SIDE AND
P.MAINCASE.CASETYPE = M.CASETYPE AND
P.MAINCASE.CASENUMBER = M.CASENUMBER AND
P.MAINCASE.CASEYEAR = M.CASEYEAR AND
P.COURTACTION !=2 and
m.benchcategory = b.benchcode and
m.empcode = m.empcode and
p.courtaction <50
group by m.empcode,p.courtaction,b.benchname,b.points
condition with p.courtaction < 50
returns
4 rows
condition with p.courtaction >50
returns
1 row
I want a result like
empcode count count1
(<50) (>50)
10 1 0
20 0 0
30 1 0
40 1 0
70 0 1
Can anyone help me out . Its very Urgent..
Thanking you
Ramesh.M
-
query
I tested the query , it got stuck , if I am not wrong that condition will go no where , actually
I want the output basing on those two condition in two columns counts
Like :
EMPNO COUNT COUNT
Thisfor Thisfor
<50 >50
10 1
20 0
30 1
70 1 (this row is coming for <50 condition)
I am preparing the report that is where I got stuck
The report looks like this
floor 1 floor2 floor3
<50 >50 <50 >50 <50 >50
10 1 1 0 1 1 1
20 1 1 1 0 1 1
30 1 1 1 1 1 1
I am getting the <50 values ,on the condition for the column count(*)*points values
another column I want hope you got my point .
Anticipation early reply.
-
Decode function
Hi
Omit the condition < 50 or > 50 in the where clause. Use the Decode
function with P.couraction to return the values you expect.
Hope this helps !
Cheers
Nandu
Never give up !
Nanda Kumar - Vellore
-
I don't think DECODE will help with a range of values. Try using CASE, something like:
SELECT . . . .
SUM(CASE WHEN p.courtaction < 50 THEN 1 ELSE 0 END) less,
SUM(CASE WHEN p.courtaction < 50 THEN 0 ELSE 1 END) more,
. . .
with no > or < condition in the WHERE clause.
(I presume you didn't want to exclude the case of EXACTLY 50 courtactions ? ! ?)
Just thought - NULL's might have to be considered . . . . ?
Last edited by DaPi; 02-27-2003 at 04:28 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Originally posted by DaPi
I don't think DECODE will help with a range of values. Try using CASE, something like:
We can use decode in this sytuation:
Code:
select ....
sum(decode(sign(p.courtaction-50), 1, 1, 0)) more_counter, sum(decode(sign(p.courtaction-50), -1, 1, 0, 1, 0)) less_counter,
...
from ...
-- small example
SQL> select
count(*),
sum(decode(sign(object_id - 1000), 1, 1, 0)) more_1000,
sum(decode(sign(object_id - 1000),-1, 1, 0, 1, 0)) less_1000
from all_objects;
COUNT(*) MORE_1000 LESS_1000
---------- ---------- ----------
4097 3119 978
-
Shestakov, you win!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
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
|