DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query

  1. #1
    Join Date
    Feb 2003
    Posts
    2

    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

  2. #2
    Join Date
    Feb 2003
    Posts
    2

    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.

  3. #3
    Join Date
    May 2002
    Posts
    108

    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

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width