0

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
  • Remove the opening parenthesis after the WHENs (both, line 10 and line 11, if I counted correctly). – sticky bit Aug 22 '18 at 0:58
  • @stickybit hangs head in shame - Rookie mistake. Now there is a null resultset, however. – JohnP Aug 22 '18 at 1:56
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'
  • Thank you for this, this worked. If you have a minute or two, ping me in chat somewhere today I have a couple small questions about the results if you can spare a moment. – JohnP Aug 23 '18 at 15:50
  • @JohnP - created a room, if you're around – RDFozz Aug 23 '18 at 16:21
0
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.