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]

Re: Sql Query help..


"Omavlana" <omavlana@redif fmail.com> wrote in message
news:fdc4fd67.0 310191914.127da 626@posting.goo gle.com...[color=blue]
> 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[/color]
record.[color=blue]
>
> 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[/color]

[John Bell]

Re: Sql Query help..

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" <omavlana@redif fmail.com> wrote in message
news:fdc4fd67.0 310191914.127da 626@posting.goo gle.com...[color=blue]
> 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[/color]
record.[color=blue]
>
> 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[/color]

[Shervin Shapourian]

Re: Sql Query help..

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(shmsharec t).

Shervin

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:3f939522$0 $9467$ed9e5944@ reading.news.pi pex.net...[color=blue]
> 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" <omavlana@redif fmail.com> wrote in message
> news:fdc4fd67.0 310191914.127da 626@posting.goo gle.com...[color=green]
> > 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[/color][/color]
each[color=blue]
> record.[color=green]
> >
> > 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[/color]
>
>[/color]