Results 1 to 2 of 2
  1. #1
    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 19:05.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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 20:25.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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