var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: quick casting problem...
Can anyone please telkl me what is wrong with this portion of a SQL statement. I have been racking my brain over this and can't seem to get it right...
I had it working fine, but when playerstats.fgm was a 0 then I got a divide by 0 error. This was the code when it was working ok as long as no one entered a 0 for fgm
(CASE playerstats.fgm WHEN 0 THEN 0 ELSE (cast(100.00 * ((cast(SUM(playerstats.fgm)) as Decimal(8,2))/(cast(SUM(playerstats.fga)) as Decimal(8,2)))) as decimal(8,1))) AS fgp
All I am trying to do is find a percentage... when playerstats.fgm = 0 then the percentage will be 0.
(cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp
Any help will be much appreciated!!!
Last edited by tnichols; 02-25-05 at 19:05.
one thing i notice is that you're mixing a scalar value in the outer CASE and an aggregate SUM value inside the CAST
and then you're setting 0 (an integer) as the THEN result, but CASTing the ELSE to 1 decimal place
plus, you're testing the wrong column for 0 divisor
finally, if "fgm" and "fga" are field goals made/attempted, then you don't have to cast them in the calculation
try this --
cast( case when sum(playerstats.fga) = 0
as decimal(8,1) ) as fgp
Last edited by r937; 02-25-05 at 20:25.