Totals on multple case conditions - mySql

Question

I am attempting to translate the query in this question into mySql, as we moved the data from an access db to a new structure.

I have come up with the following, since there are multiple conditions (Value of '' or 'Did Not Compete'), and I was getting the following error (Since resolved, see edit 2 below).

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'then 0 else 1 end) AS 'Total Weapons', SUM( case when ( tourney_info.c_we' at line 7

The query is as follows:

SELECT school_num, 'Total Form and Sparring', 'Total Weapons', 'Total Combat', 'Total Form and Sparring' + 'Total Weapons' + 'Total Combat' AS 'Total Entries'
FROM
(  
  SELECT
  tourney_info.school_num,
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Compete') AND tourney_info.spar IN ('','Did Not Compete')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Compete') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('', 'Did Not Compete') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE school_num = '546' AND tourn_date > '2018-04-20' AND tourn_date < '2018-04-22'

) AS derived

Once the error was resolved as per the comments, the query gives the following results:

school_num  Total Form and Sparring     Total Weapons   Total Combat    Total Entries   
    546     Total Form and Sparring     Total Weapons   Total Combat    0

Edit #2, after looking back at the field specifications, as a test query showed me that someone set the field to be 10 chars, and my 'Do Not Compete' was truncated on import:

If I take the interior query out as follows, I get individual totals, however I do not get a cumulative total:

  SELECT
  tourney_info.school_num, 'Total Form and Sparring' + 'Total Weapons' + 'Total Combat' AS 'Total Entries',
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE tourney_info.school_num = '546' AND tourney_info.tourn_date > '2018-04-20' AND tourney_info.tourn_date < '2018-04-22'

Result set:

 school_num     Total Entries   Total Form and Sparring     Total Weapons   Total Combat    
546               0                   36                      17                    25

Answer 1

In the latest iteration (edit #5), the problem would be that you can't use column aliases within the query where they're declared. You can either go back to the original query (get the total for each category in a sub-query, and add them together using the aliases in the main query), or do this:

SELECT
  tourney_info.school_num,
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end)
  + SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end)
  + SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Entries',
  SUM(  case when tourney_info.t_forms IN ('', 'Did Not Co') AND tourney_info.spar IN ('','Did Not Co')
        then 0
        else 1
        end) AS 'Total Form and Sparring',
  SUM( case when tourney_info.t_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Weapons',
  SUM( case when tourney_info.c_weps IN ('','Did Not Co') then 0 else 1 end) AS 'Total Combat'
FROM
  tourney_info
WHERE tourney_info.school_num = '546' AND tourney_info.tourn_date > '2018-04-20' AND tourney_info.tourn_date < '2018-04-22'

Answer 2

SUM( case when tourney_info.t_weps IN ('','Did Not Co')
     then 0 else 1 end) AS 'Total Weapons',

-->

SUM(tourney_info.t_weps NOT IN ('','Did Not Co')) AS 'Total Weapons',

This works because "boolean" expressions evaluate to either 1 (for true) or 0 (false).