unexpected result from SQL - CASE statement returns value other than stated.

Question

Hi,

Can anyone help me explain why the changed line below returns a 0 instead of the requested 11 or 12? I'm using MySQL, and this query otherwise runs, correctly... I'm attempting to catch the difference between a 0 and a null in the said field.

    SELECT SUM(CASE WHEN pr.heatEventID=1 THEN pr.points+1 ELSE '-' END) AS `100m`
, SUM(CASE WHEN pr.heatEventID=25 THEN pr.points+1 ELSE '-' END) AS `200m`
, SUM(CASE WHEN pr.heatEventID=37 THEN pr.points+1 ELSE '-' END) AS `400m`
, SUM(CASE WHEN pr.heatEventID=49 THEN pr.points+1 ELSE '-' END) AS `800m`
, SUM(CASE WHEN pr.heatEventID=61 THEN pr.points+1 ELSE '-' END) AS `1500m`
, SUM(CASE WHEN pr.heatEventID=67 THEN (CASE WHEN pr.points IS NULL THEN 11 ELSE 12 END) ELSE '' END) AS `3000m`
, SUM(CASE WHEN pr.heatEventID=69 THEN pr.points+1 ELSE '-' END) AS `Javelin`
, SUM(CASE WHEN pr.heatEventID=81 THEN pr.points+1 ELSE '-' END) AS `Shot Put`
, SUM(CASE WHEN pr.heatEventID=93 THEN pr.points+1 ELSE '-' END) AS `Discus`
, SUM(CASE WHEN pr.heatEventID=105 THEN pr.points+1 ELSE '-' END) AS `High Jump`
, SUM(CASE WHEN pr.heatEventID=117 THEN pr.points+1 ELSE '-' END) AS `Long Jump`
 FROM participation_reports AS pr
JOIN event_data on event_data.eventID=pr.heatEventID
 WHERE pr.competitorID=32

I get the result: 8 8 10 8 9 0 6 7 8 1 7

Thanks :)

** Edit was to correct the SQL to the right ctrl+V :P

Answer 1

And the solution - would be the SUM () :) Thanks to Matt Gibson for (subtley) pointing that out... of course the SUM('-') would be 0 - hence the 0.... if I remove the SUM() from around the CASE then I get what I want :)

• I was workign with old code I wasnt familiar with, so I glanced over the entire SUM() thing:)

Thanks!