Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Question Unanswered: Sql Query Help pls

    Hi, the following query returns

    select shmsharect as shares, shmctryres from shrsharemaster
    where shmctryinc = ''

    shares shmctryres
    0 SGL
    0 MY
    0 SGL
    10000 MY
    27000 SGL
    0 ID
    0 MY
    5000 MY
    0 ID
    0 JP
    3000 CN
    1000 CN
    12000 CN
    0 IN

    I have changed the query to

    select count(*)as cnt, shmsharect as shares, shmctryres from shrsharemaster
    where shmctryinc = ''
    group by shmctryres, shmsharect

    it returns

    cnt shares shmctryres

    3 16000 CN
    2 0 ID
    1 0 IN
    1 0 JP
    4 15000 MY
    3 27000 SGL


    However, I need the count seperately whose shares are > 10000. I want out put some thing like this.

    cnt1 >10000 cnt2 <10000 shmctryres

    1 12000 2 4000 CN
    0 0 2 0 ID
    0 0 1 0 IN
    0 0 1 0 JP
    1 10000 3 5000 MY
    1 27000 2 0 SGL


    Pls Help...

    Regards,
    Omav

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try something like this:

    Code:
    
    Select Sum(Decode(Sign(Shmsharect-10000),-1,0,1)) As Cnt_Ge_10000
         , Sum(Decode(Sign(Shmsharect-10000),-1,0,Shmsharect)) As Shares_Ge_10000
         , Sum(Decode(Sign(Shmsharect-10000),-1,1,0)) As Cnt_Lt_10000
         , Sum(Decode(Sign(Shmsharect-10000),-1,Shmsharect,0)) As Shares_Lt_10000
         , Shmctryres
     From Shrsharemaster
     Where Shmctryinc = ''
     Group By Shmctryres;
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •