Calculate Percentages - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate Percentages

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-07-25 : 10:14:54

I've create the below script as an example, when i try to calculate the percentage i get 0.0 as the result. Can't figure out why ?

CREATE TABLE #temp_testing_percentage (
[total] int NULL ,
[amount] int NULL ,
[percent] float NULL
)

insert into #temp_testing_percentage (total,amount,[percent])
values('104','86','')

Update #temp_testing_percentage
SET [percent] = (amount / total ) * 100.0

select *
from #temp_testing_percentage

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 10:16:21

integer divide by integer will gives you back integer


Update #temp_testing_percentage
SET [percent] = (1.0 * amount / total ) * 100.0

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 10:26:56

It's usually better to do multiplication before division.

You need to check for a divide by zero.

Also, float may not be the ideal datatype for this. For most business applications, it is better to use a datatype with an set precision and an exact representation in base 10.


update #temp_testing_percentage
set [percent] = case when amount = 0 then null else ( 100.0 * amount ) / total end

Example with a decimal datatype:


update #temp_testing_percentage
set [percent] = 
case when amount = 0 then null else convert(decimal(10,2),round(( 100.0 * amount ) / total,2)) end

CODO ERGO SUM

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-07-25 : 10:35:31

Thanks i never though about the integer divide by integer, = 0
And yes i will do the multiplication before the division.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources