Results 1 to 2 of 2

Thread: Sql Query Help

  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Question Unanswered: Sql Query Help

    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
    Sep 2003
    Location
    Germany
    Posts
    63

    Re: Sql Query Help

    Hi Omav,

    I think you should use a CASE-structure to categorize your data sets, if one is a "big" one (let's say more than 10000) or a small "one". A possible solution may look like this:

    select sum(
    case when shmsharect >= 10000 then 1
    else 0
    end
    ) as cnt_big
    , sum(
    case when shmsharect >= 10000 then shmsharect
    else 0
    end
    ) as shares_big
    , sum(
    case when shmsharect < 10000 then 1
    else 0
    end
    ) as cnt_small
    , sum(
    case when shmsharect < 10000 then shmsharect
    else 0
    end
    ) as shares_small
    , shmctryres
    from shrsharemaster
    where shmctryinc is null
    group by shmctryres


    Hope this get's you running!

    Greeting,

    Carsten


    Originally posted by omavlana
    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

Posting Permissions

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