How to count the number of courses offered by users in each city?-CodePudding

I need to count the number of courses offered by users in each city

I have the tables:

User: id, name, city_id

City: id, name

Course: id, name

Offer: id, user_id, course_id, vacancies

I need to print this report:

City  Course  Count
Milan English 67
Milan Spanish 12
Milan Italian 78
Roma  English 45
Roma  Spanish 32

MySQL code http://sqlfiddle.com/#!9/76c15e/1

CodePudding user response：

call needed to generate the report.

SELECT 
 ci.name as 'city', 
 c.name as 'course', 
 count(o.id) as 'offers'  
FROM `user` u
 LEFT JOIN `offer` o on o.user_id = u.id
 LEFT JOIN `course` c on o.course_id = c.id
 LEFT JOIN `city` ci on u.city_id = ci.id
group by u.city_id, c.id