Query

[rameshmande]

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

[rameshmande]

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.

[nandu]

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

[DaPi]

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 . . . . ?

[Shestakov]

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:


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

[DaPi]

Shestakov, you win! :)