Why do I get error code 1064 when I use count in derived table?

Question

I get error code 1064 from the count statement in the derived table. I want to count the number of times a player has occurred in any of the given positions (ergo: how many games he has played) from the games table. I'm getting the syntax wrong and can't seem to google my way to an explanation so explanation as well as solution would be very helpful. Thanks in advance.

SELECT 
    players.playerName AS Namn,
    players.playerNickName AS Smeknamn,
    players.playerBirthDate AS Födelsedatum,
    players.playerBirthPlace AS Födelseort,
    players.playerDeathDate AS Dödsdatum,
    players.playerDeathPlace AS Dödsort,
    players.playerOriginClub AS Moderklubb,
    players.playerProfession AS Yrke,
    players.playerCaps AS Landskamper,
    players.playerAllsvenskan AS Allsvensk_för_Gefle,
    CASE
        WHEN
            players.playerDeathDate IS NOT NULL
        THEN
            TIMESTAMPDIFF(YEAR,
                players.playerBirthDate,
                players.playerDeathDate)
        ELSE TIMESTAMPDIFF(YEAR,
            players.playerBirthDate,
            NOW())
    END AS År,
    players.playerCitizenship1 AS Medborgarskap1,
    players.playerCitizenship2 AS Medborgarskap2,
    players.playerRelations AS Släktskap,
    players.playerSports AS Andra_sporter,
    players.playerAltName AS Alternativt_namn,
    players.playerDeathReason AS Dödsorsak,
    CASE
        WHEN players.playerCoachID IS NOT NULL THEN 'Ja'
        ELSE 'Nej'
    END AS Gefletränare,
    CASE
        WHEN playerChairmanID IS NOT NULL THEN 'Ja'
        ELSE 'Nej'
    END AS Gefleordförande,
    players.playerPreviousClub1 AS Till_GIF_från_1,
    players.playerPreviousClubCountry1 AS Till_GIF_från_1_land,
    players.playerNextClub1 AS Från_GIF_till_1,
    players.playerNextClubCountry1 AS Från_GIF_till_1_land,
    players.playerPreviousClub2 AS Till_GIF_från_2,
    players.playerPreviousClubCountry2 AS Till_GIF_från_2_land,
    players.playerNextClub2 AS Från_GIF_till_2,
    players.playerNextClubCountry2 AS Från_GIF_till_2_land,
    players.playerPreviousClub3 AS Till_GIF_från_3,
    players.playerPreviousClubCountry3 AS Till_GIF_från_3_land,
    players.playerNextClub3 AS Från_GIF_till_3,
    players.playerNextClubCountry3 AS Från_GIF_från_1_land,
    players.playerMisc AS Kuriosa,
    player, count(*) from (SELECT 
    games.gamePos1 AS player
FROM
    games 
UNION ALL SELECT 
    games.gamePos2 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos3 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos4 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos5 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos6 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos7 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos8 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos9 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos10 AS player
FROM
    games
    UNION ALL SELECT 
    games.gamePos11 AS player
FROM
    games
    UNION ALL SELECT 
    games.gameSub1 AS player
FROM
    games
    UNION ALL SELECT 
    games.gameSub2 AS player
FROM
    games
    UNION ALL SELECT 
    games.gameSub3 AS player
FROM
    games) Matcher group by player,
players.playerNote AS Anteckning
FROM
    games
        INNER JOIN
    players ON players.playerName = games.gamePos1
        OR players.playerName = games.gamePos2
        OR players.playerName = games.gamePos3
        OR players.playerName = games.gamePos4
        OR players.playerName = games.gamePos5
        OR players.playerName = games.gamePos6
        OR players.playerName = games.gamePos7
        OR players.playerName = games.gamePos8
        OR players.playerName = games.gamePos9
        OR players.playerName = games.gamePos10
        OR players.playerName = games.gamePos11
        OR players.playerName = games.gameSub1
        OR players.playerName = games.gameSub2
        OR players.playerName = games.gameSub3
GROUP BY players.playerID
ORDER BY players.playerName

You might want to normalize your database so you don't have columns like `gamePos1` to `gamePos12` (see https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization) — Progman, Apr 26 '20 at 12:38
GamePos1 refers to goaltender, GamePos2 to left back etc so I don't think there's a problem with the database structure. But it's annoying when I want to query for a player who can occur in different positions. Do you have a better suggestion on how to structure it? — Jimmy Morén, Apr 26 '20 at 13:00
The solution is called "database normalization", which will result in a new table between `players` and `games`. So you have a table like `gameParticipants(id, gameId, playerId, position)`. — Progman, Apr 26 '20 at 14:32
Yes, I get the principle but I don’t see the point here. I’d just move the gameparticipants into a new table, but I’d still have the same problem. It’d also be more difficult to update the table if I use player-id instead of names. Suppose there would be a way around that... — Jimmy Morén, Apr 26 '20 at 18:16

nbk · Accepted Answer · 2020-04-26T13:07:42.417

Your problem lies on your count(*) From all players the syntax is wrong.

Try but without data and wanted result i only fixed the syntax error

    SELECT 
        players.playerName AS Namn,
        players.playerNickName AS Smeknamn,
        players.playerBirthDate AS Födelsedatum,
        players.playerBirthPlace AS Födelseort,
        players.playerDeathDate AS Dödsdatum,
        players.playerDeathPlace AS Dödsort,
        players.playerOriginClub AS Moderklubb,
        players.playerProfession AS Yrke,
        players.playerCaps AS Landskamper,
        players.playerAllsvenskan AS Allsvensk_för_Gefle,
        CASE
            WHEN
                players.playerDeathDate IS NOT NULL
            THEN
                TIMESTAMPDIFF(YEAR,
                    players.playerBirthDate,
                    players.playerDeathDate)
            ELSE TIMESTAMPDIFF(YEAR,
                players.playerBirthDate,
                NOW())
        END AS År,
        players.playerCitizenship1 AS Medborgarskap1,
        players.playerCitizenship2 AS Medborgarskap2,
        players.playerRelations AS Släktskap,
        players.playerSports AS Andra_sporter,
        players.playerAltName AS Alternativt_namn,
        players.playerDeathReason AS Dödsorsak,
        CASE
            WHEN players.playerCoachID IS NOT NULL THEN 'Ja'
            ELSE 'Nej'
        END AS Gefletränare,
        CASE
            WHEN playerChairmanID IS NOT NULL THEN 'Ja'
            ELSE 'Nej'
        END AS Gefleordförande,
        players.playerPreviousClub1 AS Till_GIF_från_1,
        players.playerPreviousClubCountry1 AS Till_GIF_från_1_land,
        players.playerNextClub1 AS Från_GIF_till_1,
        players.playerNextClubCountry1 AS Från_GIF_till_1_land,
        players.playerPreviousClub2 AS Till_GIF_från_2,
        players.playerPreviousClubCountry2 AS Till_GIF_från_2_land,
        players.playerNextClub2 AS Från_GIF_till_2,
        players.playerNextClubCountry2 AS Från_GIF_till_2_land,
        players.playerPreviousClub3 AS Till_GIF_från_3,
        players.playerPreviousClubCountry3 AS Till_GIF_från_3_land,
        players.playerNextClub3 AS Från_GIF_till_3,
        players.playerNextClubCountry3 AS Från_GIF_från_1_land,
        players.playerMisc AS Kuriosa,
        player, 
        (select count(*) from (SELECT 
                    games.gamePos1 AS player
                FROM
                    games 
            UNION ALL 
                SELECT 
                    games.gamePos2 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos3 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos4 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos5 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos6 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos7 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos8 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos9 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gamePos10 AS player
                FROM
                    games
                UNION ALL 
            SELECT 
                    games.gamePos11 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gameSub1 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                    games.gameSub2 AS player
                FROM
                    games
            UNION ALL 
                SELECT 
                games.gameSub3 AS player
                FROM
                    games group by player) t1 ) Matcher 
        ,
players.playerNote AS Anteckning
FROM
    games
        INNER JOIN
    players ON players.playerName = games.gamePos1
        OR players.playerName = games.gamePos2
        OR players.playerName = games.gamePos3
        OR players.playerName = games.gamePos4
        OR players.playerName = games.gamePos5
        OR players.playerName = games.gamePos6
        OR players.playerName = games.gamePos7
        OR players.playerName = games.gamePos8
        OR players.playerName = games.gamePos9
        OR players.playerName = games.gamePos10
        OR players.playerName = games.gamePos11
        OR players.playerName = games.gameSub1
        OR players.playerName = games.gameSub2
        OR players.playerName = games.gameSub3
GROUP BY players.playerID
ORDER BY players.playerName

Thanks, but now I get Error code 1248: Every derived table must have its own alias. The derived table has the alias Matcher so I'm not sure what that refers to. The table Players is a summary of each players personal data. The table Games is a summary of all games played. This query aims to join the personal data with the data about each player in the Games table. — Jimmy Morén, Apr 26 '20 at 12:53
i edited the answer, as i said such bug querys with data to run is difficult. — nbk, Apr 26 '20 at 13:08
I get error code 1054, unknown column "Player" in "field list". And if I leave out Player (before the (select count)) it doesn't throw an error but instead the count column shows 33 522 for every player so it hasn't counted each player individually. — Jimmy Morén, Apr 26 '20 at 17:19
you can't have two columns as resultset for a subquery at that position it can have only **one** result. So you must join the subquery. for count and player name, or see that you the correct data there, what ever is may be. — nbk, Apr 26 '20 at 17:27
Ok, I'm not getting anywhere, unfortunately. It won't count each player individually. Will I need a join between each select statement in the sub query as well, you mean? Can't seem to get that to work. I've got an inner join between the count and player name. — Jimmy Morén, Apr 27 '20 at 18:49
my problem i don't see what the data looks like i thought you wnated tthe count for that player in that row so thyt count (+), player is the same as players.playerName in that case you don't need the player from that Select only add a Where clause with payer = payers.name. and you would have 1 value per players.name. — nbk, Apr 27 '20 at 19:23

Why do I get error code 1064 when I use count in derived table?

1 Answers1