For each country, show the total no of departments in that country

SELECT c.COUNTRY_ID,COUNT(d.DEPARTMENT_ID)

FROM countries AS c

    JOIN 

    locations AS l 

    ON l.COUNTRY_ID= c.COUNTRY_ID

 

    JOIN  

    departments AS d 

    ON d.LOCATION_ID = l.LOCATION_ID 

GROUP BY c.COUNTRY_ID;