Getting information on users payments via subscriptions My current table db design looks like this :

- User has one subscription
- One subscription has many payments


Table subscription has a user_id, which is FK to user table.
Table payment has a subscription_id which is a FK to subscription table.

How can I count/see which users have more than one payment or at least sort them by number of payments.

This is what I tried and it doesn't look like it's doing what I want: 

SELECT count(subscription.user_id) as cnt, subscription.id FROM payment
INNER JOIN subscription ON payment.subscription_id = subscription.id
INNER JOIN user ON user.id = subscription.user_id
WHERE (payment.refunded_date is null) AND (subscription.deleted_at IS NULL)
group by subscription.id
order by cnt


What is the easiest/optimal way to accomplish this?
I find that if I try to write out what my query is doing, it tends to illuminate the answer

-- for everything that is in payment 
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of subscriptions
SELECT 
    COUNT(subscription.user_id) as cnt
,   subscription.id 
FROM 
    payment
    INNER JOIN 
        subscription 
        ON payment.subscription_id = subscription.id
    INNER JOIN 
        user 
        ON user.id = subscription.user_id
WHERE 
    (payment.refunded_date is null) 
    AND (subscription.deleted_at IS NULL)
GROUP BY 
    subscription.id
ORDER BY 
    cnt


Assuming I read your query correctly, I think your issue is that you are counting how many subscriptions an ID has. You didn't specify how the results were wrong, but I'm guessing you get 1 back.

If you want to know the total count of payments, you would count that instead.

-- for everything that is in payment 
-- that doesn't have a refund date and doesn't have a deleted at
-- And the payments have a subscription and a user
-- give me their subscription id and the count of payments
SELECT 
    COUNT(payment.subscription_id) as cnt
,   subscription.id 
FROM 
    payment
    INNER JOIN 
        subscription 
        ON payment.subscription_id = subscription.id
    INNER JOIN 
        user 
        ON user.id = subscription.user_id
WHERE 
    (payment.refunded_date is null) 
    AND (subscription.deleted_at IS NULL)
GROUP BY 
    subscription.id
ORDER BY 
    cnt