SUM the COUNT results from a database table with two different value

August 21, 2022

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

Current output:

PlayerID	EventType	Total
1	0	8
1	1	3
2	0	8
2	1	3
3	0	8
3	1	3

Expected output:

PlayerID	Total
1	11
2	11
3	11

How could I merge my current results further?

Thanks!

>Solution :

From your expected results it appears you just need to remove grouping by EventType

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;