Count total number of nested one to many relationships
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