I’d like to count total events, which can have two different values, and I could not figure out how to merge them together. My query is the following:
SELECT TOP(20) [MatchEvents].[PlayerID], [MatchEvents].[EventType], COUNT([MatchEvents].[ID]) AS [TOTAL] FROM [MatchEvents] INNER JOIN [Match] ON [MatchEvents].[MatchID] = [Match].[ID] AND [Match].[Season] = 1 WHERE ([MatchEvents].[EventType] = 0 OR [MatchEvents].[EventType] = 1) GROUP BY [MatchEvents].[PlayerID], [MatchEvents].[EventType] ORDER BY [TOTAL] ESC
How could I merge my current results further?
From your expected results it appears you just need to remove grouping by
I would suggest the following:
select top(20) me.PlayerID, Count(*) as Total from MatchEvents me join [Match] m on m.Id = me.MatchId and m.Season = 1 where me.EventType in (0, 1) group by me.PlayerID order by Total desc;