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...

(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

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

(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.

Any help will be much appreciated!!! :confused: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
then 0
else 100.00
* sum(playerstats.fgm)
/ sum(playerstats.fga)
end
as decimal(8,1) ) as fgp