Run a count query for every row ID in results

Question

I'm trying to count the number of cars at each parking lot in my app, along with some other info. But, I'm not too sure how to approach this.

Tables

I have 3 tables.

• lot - this table contains information for a parking lot
• manager - this table contains information for a parking lot's manager
• car - this table has a list of cars that can be at a given parking lot

lot
 id | name | manager_id
----+------+------------
  1 | foo  | 1
  2 | bar  | 1

manager
 id | name
----+--------
  1 | alice
  2 | bob

car
 id | lot_id
----+--------
  1 | 1
  2 | 1
  3 | 2
  4 | 2

Desired output

Given a manager_id, I want a list of:

• the name of the lot they manage
• the name of the manager
• the number of cars in that lot

This is what I want to end up with.

 id | lot_name | manager_id | manager_name | num_cars
----+----------+------------+--------------+----------
  1 | foo      | 1          | alice        | 2
  2 | bar      | 1          | alice        | 2

I've figured out how to get the manager name in the results, but I'm not sure how to get the number of cars for each lot.

This is what I have so far.

-- manager_id 1 is alice.

select
    id, name as lot_name, manager_id,
    (select name from manager where id = 1) as manager_name
from lot where manager_id = 1;

I'm thinking I need a join somewhere to bring in the car table and somehow do a count. Not exactly sure how though.

Answer 1

Im not sure if i understood correctly are you referring to a group by?

SELECT lot.id,lot.name,manager.id,manager.name,count(car.id)
FROM lot
LEFT JOIN manager ON lot.manager_id = manager.id
LEFT JOIN car ON car.lot_id = lot.id
GROUP BY lot.id,lot.name,manager.id,manager.name;