Question 398570 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Use and add the case statement into the select

I have a table where I want to add a new column based on the result.
If I create the following query it works correctly as all the tables appear plus a new call RESULT

SELECT ID,
CASE
 WHEN RESULT_HOME = RESULT_VISIT THEN "D" 
 WHEN RESULT_HOME < RESULT_VISIT THEN "W2" 
 WHEN RESULT_HOME > RESULT_VISIT THEN "W1" 
END AS RESULT
FROM matches;

But how do I add the ‘CASE’ in the following query? It fails in this example:

SELECT
   TEAM
CASE
 WHEN RESULT_HOME = RESULT_VISIT THEN "D" 
 WHEN RESULT_HOME < RESULT_VISIT THEN "W2" 
 WHEN RESULT_HOME > RESULT_VISIT THEN "W1" 
END AS RESULT
FROM
   (
      SELECT HOME AS TEAM, RESULT_HOME AS RESULT FROM matches 
      UNION ALL
      SELECT VISIT AS TEAM, RESULT_VISIT AS RESULT FROM matches
   )
   AS s 
GROUP BY
   TEAM;

The error is:

ERROR 1064 (42000): 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 'CASE
 WHEN RESULT_HOME = RESULT_VISIT THEN "D" 
 WHEN RESULT_HOME < RESULT_VI...' at line 3
Tags:

2

Answers


  1. 0

    It looks to me, that the query is correct, you’re just missing collon after TEAM.

    So

    SELECT
   TEAM,
CASE
 WHEN RESULT_HOME = RESULT_VISIT THEN "D" 
 WHEN RESULT_HOME < RESULT_VISIT THEN "W2" 
 WHEN RESULT_HOME > RESULT_VISIT THEN "W1" 
END AS RESULT
FROM
   (
      SELECT HOME AS TEAM, RESULT_HOME AS RESULT FROM matches 
      UNION ALL
      SELECT VISIT AS TEAM, RESULT_VISIT AS RESULT FROM matches
   )
   AS s 
GROUP BY
   TEAM;

    should work.

    Login or Signup to reply.
  2. 0

    If we can assume:

    • matches contains 1 row for the "match" between two teams
    • in your expected results you want 1 row EACH showing the "Result" for each individual team (so 2 rows) thus you have a union
    • and you want the team name along with their record for that match…

    Demo dbfiddle.uk

    SELECT Team, --ADD COMMA
CASE
 WHEN RESULT_HOME = RESULT_VISIT THEN "D" 
 WHEN RESULT_HOME < RESULT_VISIT THEN "W2" 
 WHEN RESULT_HOME > RESULT_VISIT THEN "W1" 
END AS RESULT
FROM 
   ( --Since you want 2 rows per match union, but you need the 
     --result_home and Result_visit in each select for the case to work. 

      SELECT HOME AS TEAM, RESULT_HOME, RESULT_VISIT FROM Matches 
      UNION ALL
      SELECT VISIT AS TEAM, RESULT_HOME, RESULT_VISIT  FROM Matches
   ) AS s ;
--ELIMIATE GROUP BY you have no aggregation, so it's not needed.  unless you have duplicate data in matches...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search