Counting rows in different tables and grouping by a certain criteria

I have 3 tables: Projects, Components, Suppliers. Each one has an id column, city column and some other columns.

What I am trying to do is count how many projects, components and suppliers there are for each city.

What I have tried is:

SELECT p.city, COUNT(p.idp), COUNT(c.idc), COUNT(s.idf) FROM
Projects p, Components c, Suppliers s
GROUP BY p.city

After running this query, I am getting incorrect values, almost all of them are the same, like it’s shown here:

I only have one project, 1 or 2 components and 1-3 suppliers stored for each city so these are not the expected results

After looking for some similar solved problems I came up with this piece of code

SELECT p.city , p.nr_projects, c.nr_components, s.nr_suppliers
FROM 
(SELECT city, COUNT(idp) AS nr_projects
FROM Projects
GROUP BY city)
AS p
JOIN
(SELECT city, COUNT(idc) AS nr_components
FROM Components
GROUP BY city)
AS c
ON p.city=c.city
JOIN
(SELECT city, COUNT(idf) AS nr_suppliers
FROM Suppliers
GROUP BY city)
as f
ON p.city=f.city
GROUP BY p.city;

This gave me an error saying that the SQL command was not properly ended.

How should I approach this task?

Answer

Since you are no longer performing any aggregation at the top level of your query, you no longer need a GROUP BY clause. Also, for Oracle, you cannot use an AS in the table alias expression. This should work:

SELECT p.city , p.nr_projects, c.nr_components, s.nr_suppliers
FROM 
    (SELECT city, COUNT(*) AS nr_projects
    FROM Projects
    GROUP BY city) p
JOIN
    (SELECT city, COUNT(*) AS nr_components
    FROM Components
    GROUP BY city) c ON p.city=c.city
JOIN
    (SELECT city, COUNT(*) AS nr_suppliers
    FROM Suppliers
    GROUP BY city) s ON p.city=s.city

Note that if a city might not have projects, components or suppliers then you should use a FULL OUTER JOIN in place of JOIN to ensure you still get rows for that city in the output.

Note also that it is more efficient to use COUNT(*) than to count a particular variable (as there is no need to check for NULL values) and I have made that modification to the query.