SQL query help...

[Omavlana]

Hi,

I have writtem the query....

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 = ''
group by shmctryres


The above query returns the output as

cnt_big shares_big cnt_small shares_small shmctryres

0 0 3 3000 China
1 10000 2 0 Indonesia
0 0 1 0 India
2 22000 1 4000 Japan
0 0 4 0 Malaysia
1 27000 2 8000 Singapore


I would like to add two colums to calculate the count.

One column as col1 to calculate sum cnt_big + cnt_small for each record

Another column as col2 to calculate sum shares_big + shares_small for each record.

I need the output like below...

cnt_big shares_big cnt_small shares_small shmctryres col1 col2

0 0 3 3000 China 3 3000
1 10000 2 0 Indonesia 3 10000
0 0 1 0 India 1 0
2 22000 1 4000 Japan 3 26000
0 0 4 0 Malaysia 4 0
1 27000 2 8000 Singapore 3 35000


Pls help..

Regards,
Omav

 

[Microsoft News Groups]

Omav,

I haven't checked the syntax but you could try something like this.

Select A.*, A.cnt_big + A.cnt_small As col1, A.shares_big + A.shares_small
As col2 From
(
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 = ''
group by shmctryres) A