How to count rows in nested tables with one SQL query?

Question

I have three tables. Each User can have multiple Subscriptions and each Subscription can have multiple Payments.

Me goal is to count all Payments for a single User using one SQL query. Is it possible to do and how?

In the case below, The result for a User with id 1 should be 2 (because the User has two Payments)

Users
+----+------+
| Id | Name |
+----+------+
| 1  | John |
+----+------+

Subscriptions
+----+--------+-----------+
| Id | userId |   data    |
+----+--------+-----------+
| 1  |   1    | some data |
+----+--------+-----------+
| 2  |   1    | some data |
+----+--------+-----------+

Payments

+----+----------------+--------+
| Id | subscriptionId | amount |
+----+----------------+--------+
| 1  |        1       |   30   |
+----+----------------+--------+
|  2 |        2       |   50   |
+----+----------------+--------+

cn0047 · Accepted Answer · 2019-02-07 13:13:21Z

3

try like below by using join and aggregation

SELECT u.id, u.Name, COUNT(p.id) AS numberofpayment
FROM users u
  Left JOIN Subscriptions s ON u.Id=s.userId
  Left JOIN Payments p ON s.id=p.subscriptionId
GROUP BY u.id, u.Name

edited Feb 7, 2019 at 13:13

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

answered Feb 5, 2019 at 12:17

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kickstart Over a year ago

Might be best to use LEFT OUTER JOINs to allow a count of 0 when the user exists but has no payments.

cn0047 Over a year ago

@Kickstart This answer is good to count all Payments for a single User.

Kickstart Over a year ago

@VladimirKovpak - if the user has no subscriptions or no payments the query as it stands will return no rows, rather than a row for the user with a count of 0.

cn0047 Over a year ago

@Kickstart Yes, true... It's better to use LEFT JOIN.

Zaynul Abadin Tuhin Over a year ago

Hey guys thanks I edited you are right left is more meaningful

cn0047 · Accepted Answer · 2019-02-05 13:38:04Z

1

You can try to do something like this:

SELECT COUNT(p.Id) AS PaymentCount
FROM Users u
  LEFT JOIN Subscriptions s ON u.Id=s.userId
  LEFT JOIN Payments p ON s.id=p.subscriptionId
WHERE u.Id = @yourUserID

Pay attention on COUNT(p.Id) - it means count of existing payments.

PS: this answer for @Kickstart.

answered Feb 5, 2019 at 13:38

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Collectives™ on Stack Overflow

How to count rows in nested tables with one SQL query?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related