Using 2 “Group By” in one SQL Query

I got 3 tables that look like this:

Locations: id,name

Events: id,name,locationid

Participants: id,name,eventid

What I want is a query, that returns all locations, a count of the events linked to it and a count of the participants (indirectly) linked to a location.

This is what I’m trying, but this query returns a row for each tournament, instead of grouping it:

SELECT a.id,a.name,count(b.id),count(c.id) FROM Locations a
LEFT JOIN Events b ON a.id=b.locationid
LEFT JOIN Participants c ON b.id=c.eventid
GROUP BY b.locationid,c.eventid

Answer

One method is to use count(distinct) instead of count().

SELECT l.id, l.name, count(distinct e.id), count(distinct p.id)
FROM Locations l LEFT JOIN
     Events e
     ON l.id = e.locationid LEFT JOIN
     Participants p
     ON e.id = p.eventid
GROUP BY l.id, l.name;