Count total number of nested one to many relationships Tables: Users, Costs, Payments

Users can have many costs. Costs can have many payments

I want to get information about the user AND count of all payments that they made

This query returns result but takes very long time to run

SELECT u.id, u.name,
  (
    SELECT COUNT(p.id)
    FROM payments AS p
    WHERE p.cost_id IN (SELECT cost_id FROM costs WHERE user_id = u.id)
  ) AS payments_made
FROM users as u;


I tried outer joins but then it return duplicate if user has more than one cost

Inner join dropped all users that didn't have a cost
Let me know if this covers your desired result.




create table users (user_id int primary key, name varchar(20));
insert into users values(1, 'john'), (2, 'anne'), (3, 'peter');

create table costs(cost_id int primary key, user_id int);
insert into costs values (1, 1), (2, 1), (3, 2);

create table payments(payment_id int primary key, cost_id int);
insert into payments values
(1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 3);
GO



13 rows affected






select   c.user_id, count(*) num_payments
from     payments p
join     costs c
on       p.cost_id = c.cost_id
group by c.user_id
;
GO



user_id | num_payments
------: | -----------:
      1 |            5
      2 |            2






select    u.user_id, u.name, coalesce(p.num_payments, 0) num_payments
from      users u
left join (select   c.user_id, count(*) num_payments
           from     payments p
           join     costs c
           on       p.cost_id = c.cost_id
           group by c.user_id) p
on         u.user_id = p.user_id           
GO



user_id | name  | num_payments
------: | :---- | -----------:
      1 | john  |            5
      2 | anne  |            2
      3 | peter |            0



dbfiddle here

Or better a left join solution:




select    u.user_id, u.name, count(p.payment_id) num_payments
from      users u
left join costs c
on        u.user_id = c.user_id
left join payments p
on        p.cost_id = c.cost_id
group by  u.user_id, u.name
;
GO



user_id | name  | num_payments
------: | :---- | -----------:
      1 | john  |            5
      2 | anne  |            2
      3 | peter |            0



dbfiddle here