CODEWITHSUNDEEP
sqloracle-databasecounting
MCM
MCM

Reputation: 61

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: Results

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?

Upvotes: 1

Views: 53

Answers (2)

LukStorms
LukStorms

Reputation: 29677

Union the totals, then aggregate.

That way you'll also have totals for cities that are missing in 1 of the tables.
Which wouldn't be in the result when using INNER JOIN. (but they would with FULL JOIN)

SELECT city, 
SUM(proj) AS projects, 
SUM(comp) AS components, 
SUM(supp) AS suppliers
FROM 
(
    SELECT city
    , COUNT(idp) AS proj
    , 0 AS comp
    , 0 AS supp
    FROM Projects
    GROUP BY city

    UNION ALL

    SELECT city, 0, COUNT(idc), 0
    FROM Components
    GROUP BY city

    UNION ALL

    SELECT city, 0, 0, COUNT(idf)
    FROM Suppliers
    GROUP BY city
) q
GROUP BY city
ORDER BY city

Upvotes: 1

Nick
Nick

Reputation: 147236

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.

Upvotes: 2

Related Questions