SQL Server的按凹形列分组问题

Question

我试图用concat列根据查询结果进行分组，但我被它卡住了。

这就是我的疑问：

SELECT 
CONCAT (a.athleteLastName, ', ', a.athleteFirstName, ' (', a.athletePosition, ')') AS "Athlete",
COUNT(M.awardName) AS "Number of Awards Won" 
FROM 
award m
FULL OUTER JOIN 
winner w ON m.awardId = w.awardId
FULL OUTER JOIN  
athlete a ON w.athleteId = a.athleteId
GROUP BY 
M.awardId, athleteLastName, a.athleteFirstName,a.athletePosition
ORDER BY 
M.awardId DESC, Athlete;

这就是结果：

Trout, Mike (OF)    1
Arenado, Nolan (3B) 4
Arenado, Nolan (3B) 7
Trout, Mike (OF)    3

但我想按玩家对奖项进行分组，结果应该是这样的：

Arenado, Nolan (3B) 13
Trout, Mike (OF)    4

Answer 1

Arenado, Nolan (3B)如何变成13？如果只是简单地总结奖励，您可以使用子查询，并可以尝试以下操作：

select "Athlete", sum("Number of Awards Won") as awards
from
(SELECT CONCAT (a.athleteLastName, ', ',a.athleteFirstName,' (',a.athletePosition,')') AS "Athlete" ,count(M.awardName) AS "Number of Awards Won" FROM award m
FULL OUTER JOIN winner w ON m.awardId = w.awardId
FULL OUTER JOIN  athlete a ON w.athleteId = a.athleteId
GROUP BY M.awardId, athleteLastName, a.athleteFirstName,a.athletePosition
ORDER BY M.awardId DESC, Athlete )  as a group by 1;