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

[John Bell]

"Omavlana" <om******@rediffmail.com> wrote in message
news:fd**************************@posting.google.c om...

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

[John Bell]

Hi

As cnt_big is shmsharect >= 10000 and
cnt_small is shmsharect < 10000

then cnt_big + cnt_small should be count(*)

similarly shares_big + shares_small should be SUM(shmsharect)

John

"Omavlana" <om******@rediffmail.com> wrote in message
news:fd**************************@posting.google.c om...

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

[Shervin Shapourian]

John,

I just want to add a tiny little thing to your post. If shmsharect is null,
then cnt_big + cnt_small is different from count(*). So it's better to use
COUNT(shmsharect).

Shervin

"John Bell" <jb************@hotmail.com> wrote in message
news:3f**********************@reading.news.pipex.n et...

Hi

As cnt_big is shmsharect >= 10000 and
cnt_small is shmsharect < 10000

then cnt_big + cnt_small should be count(*)

similarly shares_big + shares_small should be SUM(shmsharect)

John

"Omavlana" <om******@rediffmail.com> wrote in message
news:fd**************************@posting.google.c om...

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