# Thread: quick casting problem...

1. Registered User
Join Date
Feb 2005
Posts
7

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

Code:
`(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

Code:
`(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!!!
Last edited by tnichols; 02-25-05 at 18:05.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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 --
Code:
``` 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```
Last edited by r937; 02-25-05 at 19:25.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•