0

Any help to understand what is (not) happening would be appreciated. I have read most questions about sum NULL issues, googled and thought I understood how to deal with this; but in this bit of code I just can't see the solution.

The Brief: For my fellow players I would like to sum top 14 scores from the 22 rounds of our competition.

Working through the code for several days I have the result almost working, BUT just can't work a solution to the sum NULL issue. Player 5 should be scoring 15 not a NULL.

As soon as I increase the offset in the limit statement the query starts to attempt to add a NULL.

Help: Need to understand where I have failed and how to correct the query.

Thanks in advance.

idPlayerDetails
   Rnd1 Rnd2 Rnd3 Rnd4 Rnd5 Rnd6 Rnd7 TopThree
1   0   19   18   15   18   24   0     61
2   0   21   18   10   17   13   0     56
3   0   15    0   17   17   23   0     57
4   0   21   21   21   18   19   0     63
5   0   0     0    0    0   15   0     NULL
6   0   24   20   20   18   20   0     64
8   0   25   18   14    0   25   0     68
9   0   0     0    0    0   19   0     NULL

SELECT idPlayerDetails
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 1 THEN RoundScorecardPlayerPoints END),0) AS Rnd1
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 2 THEN RoundScorecardPlayerPoints END),0) AS Rnd2
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 3 THEN RoundScorecardPlayerPoints END),0) AS Rnd3
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 4 THEN RoundScorecardPlayerPoints END),0) AS Rnd4
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 5 THEN RoundScorecardPlayerPoints END),0) AS Rnd5
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 6 THEN RoundScorecardPlayerPoints END),0) AS Rnd6
     , IFNULL(MIN(CASE WHEN CompetitionRoundID = 7 THEN RoundScorecardPlayerPoints END),0) AS Rnd7
     , SUM(CASE WHEN (RoundScorecardPlayerPoints, idRoundScorecard) >= (
        SELECT RoundScorecardPlayerPoints, idRoundScorecard
        FROM RoundScorecard AS x
        WHERE x.idPlayerDetails = y.idPlayerDetails
        ORDER BY RoundScorecardPlayerPoints DESC, idRoundScorecard DESC
        LIMIT 2, 1
     ) THEN RoundScorecardPlayerPoints END) AS TopThree
FROM RoundScorecard y
GROUP BY idPlayerDetails
ereceles
  • 13
  • 4
  • 1
    Have you tried using COALESCE (https://stackoverflow.com/questions/17104329/how-to-use-coalesce-in-mysql)? – Rob Moll Oct 30 '20 at 11:47
  • Your case has no else so use else 0 , simply in line 5 and 9 you have the first 5 rounds NULL – nbk Oct 30 '20 at 11:50
  • Thks Rob - no, not on the final sum query. How does it differ from IFNULL? – ereceles Oct 30 '20 at 11:58
  • NBK - Thank you. But that is a good clue in the right direction. I think "else 0" in line 5 & 9 won't sum the row for best of 3. Still I can't believe I missed that. I'll give that a go - thks – ereceles Oct 30 '20 at 23:29

0 Answers0