Calculating Percentage for each line

[bmacbmac]

Hi,

In my table I have three colums with the following data:

Market Amount AmountPercent
CT 705000 NULL
FAHT 1100000 NULL
LT 441000 NULL
STG 221100 NULL

I am trying to set the 'AmountPercent' column to the percentage of total amount sum.

I'm trying this:

select amount/sum(amount)from #temp
group by market, amount

But it just gives my percentage as 1.0000 for each line. I'm probably grouping wrong, but everything I try gives me a 1.0000 percentage.

Any ideas?

Thanks!

 

[bmacbmac]

Sorry, my code says I am doing a 'select' statement, but I actually need to do an update.

Also, this is SQL 2000.

Thanks for looking!

 

[gmmastros]

Your grouping is wrong. And you may run in to integer math issues (if your amount column is an integer)

I would suggest you do this in parts. Like this...

Declare @Total Int
Select @Total = Sum(Amount) From #Temp

Update #Temp
Set    AmountPercent = 100.0 * Amount / @Total

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros.... worked like a champ!