0

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

1

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

  • Thank you very much! The first one worked perfectly! Left join solution works as well but it's not as easy to add more columns from user. Well done :) – Ruslan Feb 6 '18 at 20:47
  • I'm glad to help – McNets Feb 6 '18 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.