SQL - COUNT columns from separate tables based on a certain value

user_14420833 0
Viewed 58

I'm still learning SQL and this is part of my assignment. I'm not just looking for the answers but I would like to understand how to tackle this as I'm stumped as to what to even search for online.

Part of the assignment we are given a question and an example output. We have to insert data to tables we have created and write a SQL query that will return results that answer the question.

This particular question goes something like: "For each office, display its suburb, the name of the manager, the total number of staff (excluding the manager) in the office, and the number of listings that are for sale."

There are 3 tables involved (I removed unnecessary columns):

  • Office (id, office_manager_id, office_suburb)
  • Staff (id, office_id)
  • Listing (id, office_id, staff_id, buyer_id)

The output example is something like:

Office Suburb |  Manager  |  No. of Staff  |  No of Listing for Sale  |
  New York    |  Johnny   |      2         |         5                |

I figured I could do this with a COUNT and GROUP BY, but as soon as I try to join 3 tables and use COUNT, the number of rows just goes crazy and so my count for 'No. of Staff' and 'No. of Listing for Sale' becomes the same.

If I count them separately (only using 2 tables, e.g. Office and Listing or Office and Staff) then I am able to get the desired COUNT output.

This is what I got so far:

SELECT 
    o.office_suburb AS "Office Suburb", 
    (s.first_name || ' ' || s.last_name) AS "Manager",
    COUNT(s.salary)-1 AS "No. of Staff", 
    COUNT(l.id) AS "No. of Listings for Sale"
FROM staff s, office o, listing l
WHERE o.id = s.office_id 
    AND s.id = o.office_manager_id 
    AND o.id = l.office_id 
    AND s.id = l.staff_id
    AND l.buyer_id IS NULL
GROUP BY o.office_suburb, s.first_name, s.last_name

If anyone could help me with the direction I would appreciate it. I tried searching for terms like 'count multiple tables', 'group by with 3 tables', etc with no luck. Even if you could help with something like: 'just search xyz, that's what you need', I would so appreciate that...

Thank you.

PS: The way we learnt how to join tables was to use the WHERE clause, which is why my SQL is using WHERE instead of JOIN. Also this is all done in Oracle ApEx.

oracle-apex sql
2 Answers

I would recommend correlated subqueries, but like this:

SELECT o.office_suburb, (sm.first_name || ' ' || sm.last_name) AS Manager,
       (SELECT COUNT(*)
        FROM staff s2
        WHERE s2.office_id = o.id
       ) as num_staff,
       (SELECT COUNT(*)
        FROM listings l
        WHERE l.office_id = o.id AND l.buyer_id IS NULL
       ) as num_listings
FROM office o JOIN
     staff sm
     ON sm.id = o.office_manager_id ;

Note the use of proper, explicit, standard, readable JOIN syntax. Also, GROUP BY is not needed in the outer query.

user_11440350

The point is that each office has a unique id per office_manager and office_suburb so something like the following would help. Since my initial answer was wrong, i have to edit accordingly. You can use JOIN as in the following query.

SELECT
o2.office_suburb AS "Office Suburb",
(s2.first_name || ' ' || s2.last_name) AS "Manager",
p.csid AS "No. of Staff", 
p.clid AS "No. of Listings for Sale" 
FROM
(SELECT 
    o.id,
    COUNT(s.id) AS csid, 
    COUNT(l.id) AS clid
FROM office o
JOIN staff s ON (o.id = s.office_id)
JOIN listing l ON (o.id = l.office_id AND s.id = l.staff_id)
WHERE l.staff_id <> o.office_manager_id AND l.buyer_id IS NULL
GROUP BY o.id) p
JOIN office o2 ON p.id = o2.id
JOIN staff s2 ON s2.id = o2.office_manager_id AND s2.office_id = o2.id

You could check the result in SQL Fiddle

Admin1
Related