Sql Query Help pls

omavlana · #1 (**permalink**) 10-17-03, 04:02

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

LKBrwn_DBA · #2 (**permalink**) 10-17-03, 15:23

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech