Warm tip: This article is reproduced from stackoverflow.com, please click
group-by mysql count left-join

MYSQL GROUP BY LEFT JOIN and COUNT

发布于 2020-03-31 22:56:09

I am having a problem grouping and counting items in a MYSQL database with JOIN clause

My two tables are as follows

users table

id | surname | othernames
1  |  Doe    | John
2  |  Doe    | Jane
3  |  Doe    | Mary

subscriptions table

id | user_id | parent_subscription_id 
1  | 1       | Null
2  | 2       | 1
3  | 3       | 1
4  | 4       | 2                         
5  | 5       | 3
6  | 6       | 3

I need to be able to produce a list as follows

Name         |   Referrals
John Doe     | 2
Jane Doe     | 1
Mary Doe     | 2

In other words,it Is the user in users table with the users.id which matches subscriptions.user_id that has the subscription with subscriptions.id which is a parent subscription to another subscription. That means, if your subscription is referenced by another subscription as its own parent_subscription_id, then that new subscription becomes your referral.

I have tried the following query and it is not giving me the expected results

SELECT users.surname, users.othernames,count('s.parent_subscription_id') as referrals 
FROM users 
    LEFT JOIN subscriptions s ON s.user_id=users.id 
group BY parent_subscription_id

I have checked some other questions on SO but I have not been able to find any that solves this type of issue Thank you

Questioner
Josh
Viewed
30
Josh 2020-02-02 19:28

This query eventually gave me the result I have been looking for

SELECT u.surname,u.othernames,s1.id,s1.parent_subscription_id, 
s1.refcode, IFNULL(count(s2.parent_subscription_id),0) as referrals 
FROM `subscriptions` s1 left join subscriptions s2 
on s1.id=s2.parent_subscription_id 
LEFT JOIN users u ON u.id=s1.user_id
GROUP by s1.id

Thank you all for your guidance and support on this. I deeply appreciate it