Dimko 696's questions

Gostaria de obter o nome, sobrenome e a contagem de jogadores visualizados presentes na coluna Viewed_PlayerIDda tabela ViewedPlayers de cada usuário, cujo ID de usuário está presente na coluna CreatedByCharacterIDda tabela ViewedPlayers. Dos quais o que viewed_playerIDestá marcado como "jogador de equipe" na coluna UserTypeda tabela Users.

Mesa Users:

Mesa ViewedPlayers:

Resultado esperado:

Eu tentei 2 consultas diferentes

Consulta nº 1:

SELECT      
    c.Firstname, c.LastName,
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) AS Count
FROM 
    Users c
JOIN    
    ViewedPlayers p ON p.CreatedByCharacterID = c.ID
WHERE   
    c.UserType = 'Teamplayer'
GROUP BY 
    p.CreatedByCharacterID
ORDER BY 
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) DESC

Consulta nº 2:

SELECT      
    c.Firstname, c.LastName,
    SUM(CASE WHEN c. UserType = 'Teamplayer' AND p.CreatedByCharacterID THEN 1 ELSE 0 END) AS Count
FROM    
    Users c
JOIN
    ViewedPlayers p ON p.CreatedByCharacterID = c.ID
GROUP BY 
    p.CreatedByCharacterID
ORDER BY 
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) DESC