I'm trying to create a query which will select a team from a declared variable and then make the remaining teams "anonymous" by giving them generic brands and sequential IDs.
For example, if my dataset has 3 different team names (ABC, DEF, and GHI), but I would only like the true value of the 1 chosen team (ABC) displayed. Here is the skeleton of the query I'm working with:
SET @teamid = 123;
SELECT CASE WHEN ID = @teamid
THEN NAME
ELSE 'Team' + ' - ' + SEQ
END AS 'Team Name',
SUM(TOTAL) AS 'Team Total'
FROM TEAM
GROUP BY 1;
I would like the results to look something like this:
Team Name: Team Total:
ABC 100
Team - 1 50
Team - 2 150
How can I go about creating a query which will create a unique number that I can replace the original team name with? I know I have to replace the "SEQ" portion of the case statement, but I'm not sure what exactly to replace it with. Also, it is important that each team has the same ID whether or not it is anonymous (so if team DEF has 50 rows, it should be shown as Team - 1 only as opposed to Team - 1-50) so that my groupings will work properly.
Thanks for the help.
Use a user-defined variable that you increment. Also, MySQL uses CONCAT to concatenate strings, not +.
SET @teamid = 123;
SELECT CASE WHEN ID = @teamid
THEN NAME
ELSE CONCAT('Team - ', @SEQ)
END AS 'Team Name',
SUM(TOTAL) AS 'Team Total',
@SEQ := CASE WHEN ID = @teamid
THEN @SEQ
ELSE @SEQ + 1
END
FROM TEAM
CROSS JOIN (SELECT @SEQ := 1) AS vars
GROUP BY ID;