Sql query... help

[Omavlana 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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

SELECT COUNT(CASE WHEN shmsharect>=10000 THEN 1 END) AS cnt1,
SUM(CASE WHEN shmsharect >=10000 THEN shmsharect END) AS shares_10k,
COUNT(CASE WHEN shmsharect< 10000 THEN 1 END) AS cnt2,
SUM(CASE WHEN shmsharect < 10000 THEN shmsharect END) AS shares_under_10k,
shmctryres
FROM shrsharemaster
WHERE shmctryinc = ''
GROUP BY shmctryres

(untested)

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

See replies in microsoft.public.sqlserver.programming.

Please don't multi-post.

--
David Portas
------------
Please reply only to the newsgroup
--

[Omavlana Omavlana]

Thank you very much.

Omavlana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!