Search code examples
mysqlgroup-bysumcaseunion-all

MySQL Where Clause with Union All getting wrong results

I will preface this by saying I am still very much learning MySQL, and I am absolutely at that stage where I know just enough to be dangerous.

I have a database with data for scorekeeping for a sports league. We record wins/losses as either 1 or zero points. There is a night that has double play involved (meaning the players play twice in a single night, for 2 different formats). My data is structured like so (just a sample, I have hundreds of rows, over different formats):

ID FID WK Type HomeTeam AwayTeam HF1 HF2 AF1 AF2
1 44 1 PL TM1 TM2 1 0 0 1
2 44 1 PL TM3 TM4 0 0 1 1
3 44 2 PL TM2 TM3 1 1 0 0
4 44 2 PL TM4 TM1 0 1 1 0
5 44 3 PL TM3 TM1 999 0 999 1
6 44 3 PL Tm2 TM4 1 0 0 1

Where the 999 is used as a code number for us to know that the match hasn't yet been played, or the scoresheet hasn't been turned in to us for recordkeeping. (I use PHP to call these to a website for users to see what is going on, and am using an IF statement to convert that 999 to "TBD" on the website)

I can pull the Format 1 and Format 2 scores separately and get a listing just fine, but when I try to pull them together and get a total score, I am getting an incorrect count. I know the error lies with my WHERE Clause, but I've been banging my head trying to get it to work correctly, and I think I just need an extra set of eyes on this.

My current SQL Query is as follows:

SELECT Team, 
       SUM(TotalF1) AS TotalF1, 
       SUM(TotalF2) AS TotalF2, 
       SUM(TotalF1+TotalF2) AS Total
FROM ( ( SELECT HomeTeam AS Team, 
                HF1 AS TotalF1, 
                HF2 AS TotalF2 
         FROM tbl_teamscores 
         WHERE FID = 44 
           AND Type = 'PL' 
           AND HF1 != 999 
           AND HF2 != 999 ) 
       UNION ALL 
       ( SELECT AwayTeam, 
                AF1, 
                AF2 
         FROM tbl_teamscores 
         WHERE FID = 44 
           AND Type = 'PL' 
           AND AF1 != 999 
           AND AF2 != 999 )
      ) CC 
GROUP BY Team 
ORDER BY Total desc, Team ASC;

I am getting incorrect totals though, and I know the reason is because of those 999 designations, as the WHERE clause is skipping over ALL lines where either home or away score matches 999.

I tried separating it out to 4 separate Select Statements, and unioning them, but I just get an error when I do that. I also tried using Inner Join, but MySQL doesn't seem to like that either.

Edit to add DBFiddle with Real World Table Data and queries: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1d4d090b08b8280e734218ba32db6d88 An example of the problem can be observed when looking at the data for Player 10. The overall total should be 13, but I am only getting 12.

Any suggestions would be very helpful.

Thanks in advance!

Solution

  • You can use conditional aggregation:

    SELECT Team, 
       SUM(CASE WHEN Total8 <> 999 THEN Total8 END) AS Total8, 
       SUM(CASE WHEN TotalLO <> 999 THEN TotalLO END) AS TotalLO, 
       SUM(CASE WHEN Total8 <> 999 THEN Total8 END) + SUM(CASE WHEN TotalLO <> 999 THEN TotalLO END) AS Total
FROM (
  SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
  UNION ALL 
  SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
) CC 
GROUP BY Team 
ORDER BY Team ASC;

    or:

    SELECT Team, 
       SUM(NULLIF(Total8, 999)) AS Total8, 
       SUM(NULLIF(TotalLO, 999)) AS TotalLO, 
       SUM(NULLIF(Total8, 999)) + SUM(NULLIF(TotalLO, 999)) AS Total
FROM (
  SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
  UNION ALL 
  SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
) CC 
GROUP BY Team 
ORDER BY Team ASC;

    If you get nulls in the results then you should also use COALESCE():

    SELECT Team, 
       COALESCE(SUM(NULLIF(Total8, 999)), 0) AS Total8, 
       COALESCE(SUM(NULLIF(TotalLO, 999)), 0) AS TotalLO, 
       COALESCE(SUM(NULLIF(Total8, 999)), 0) + COALESCE(SUM(NULLIF(TotalLO, 999)), 0) AS Total
FROM (
  SELECT HomeTeam AS Team, Home8PTS AS Total8, HomeLOPTS AS TotalLO FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
  UNION ALL 
  SELECT AwayTeam, Away8PTS, AwayLOPTS FROM tbl_teamscores WHERE FID = 44 AND Type = 'PL' 
) CC 
GROUP BY Team 
ORDER BY Team ASC;

    See the demo.