Retrieving aggregate values from multiple tables

Question

I'm trying to get some aggregate values from different tables, but my problem is that they're being returned incorrectly, i.e. as multiplications of each other.

I've got these tables:

Institutes
----------
ID
name

Conversions
-----------
ID
institute_id
training_id

Trainings
---------
ID
institute_id

And I want to do get the Institute.name, the number of conversions and the number of trainings. I can get the name and one of the aggregate values, but when I try to get both values it returns the wrong number, because it will repeat all conversions for each training (or the other way round).

I'm using this SQL:

SELECT    Institute.id
          ,Institute.name 
          ,Institute.friendly_url
          ,count(Training.`id`) as totalTrainings
          ,count(Conversion.`id`) AS totalConversions
FROM      institutes Institute  
          LEFT JOIN    trainings Training     ON Institute.`id` = Training.`institute_id`       
          LEFT JOIN    conversions Conversion ON Training.`institute_id` =   Conversion.`institute_id`
GROUP BY  1,2,3

I need to add more tables later on, using the same structure and also with the sole purpose of retrieving aggregates. I can make it work by using multiple queries, but then I'd run into the problem of not being able to use proper sorting, pagination and problems when I want to filter the data.

Creating a view is a solution I've been thinking about too, but I still need some SQL to create that view with.

I've also tried tinkering with the GROUP BY but to no avail.

Update I'm now using subqueries in my join, which seems to work. Is there a more optimal solution though?

SELECT    Institute.id
          ,Institute.name 
          ,Institute.friendly_url
          ,Training.total
          ,Conversion.total
FROM      institutes Institute 
          LEFT JOIN (SELECT institute_id, count(id) AS total
                     FROM `trainings`
                     GROUP BY institute_id) AS Training ON Training.institute_id = Institute.id
          LEFT JOIN (SELECT institute_id, count(id) AS total
                     FROM `conversions`
                     GROUP BY institute_id) AS Conversion ON Conversion.institute_id = Institute.id

Answer 1

Try that. Doing a separate subqueries is better IMHO. And looks better also, easier to read the query.

SELECT Institute.id
,Institute.name
,Institute.friendly_url
,(SELECT count(Training.id) FROM trainings Training WHERE Institute.id = Training.institute_id LIMIT 1) as totalTrainings
,(SELECT count(Conversion.id) FROM conversions Conversion ON Institute.id = Conversion.institute_id LIMIT 1) AS totalConversions
FROM institutes Institute