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
WHEN
s (both, line 10 and line 11, if I counted correctly).