Division in SELECT 1

[dBjason]

SQL Server 7

Hello Again,

I'm trying to calculate each row's unit percentage of total units. Here's what I'm trying:

select  

  stage, 
  b.units, 
  (select sum(a.units) from #temptable2 a) as TotalUnits,
  (b.units / (select sum(a.units) from #temptable2 a) * 100) as PercentUnits

from #temptable2 b

Here's what I get:

stage  units  T.U.  P.U.

bob      2     6     0
sally    3     6     0
tim      1     6     0

Seems to me I should be getting 33.3%, 50%, and 16.6%, respectively. Does SQL not like to divide in the select statement?

Thanks in advance,
Jason

 

[SQLDenis]

convert to decimals or add * 1.00 to one of the values

take a look

select 3/2
select convert(decimal(5,2),3)/convert(decimal(5,2),2)
select 3/(2*1.00)

you could do

((b.units *1.00)/ (select sum(a.units)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dBjason]

Ahhh... I see! (or so said the blind man)

Thanks again, Denis!