MYSQL GROUP BY LEFT JOIN and COUNT

Question

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

Its not clear to me what results you are trying to get. How does John Doe have 2 referrals? Is it that the `subscription.id` associated with his `user_id` is used as the `subscription.parent_subscription_id` in two records? — Moob, Jan 31 '20 at 11:59
@Moob, yes. It Is the user in users table with the users.id which matches subscriptions.user_id that has the subscription which is a parent subscription. In order words, if your subscription is referenced by another subscription as its own parent_subscription_id, then that new subscription becomes your referral — Josh, Jan 31 '20 at 12:04
Its been removed from the question but here's a [sqlFiddle](http://sqlfiddle.com/#!9/5b5ac6/2/0) matching the OP's schema. — Moob, Jan 31 '20 at 12:35
Please put all that is needed for your question in your post as text, not just elsewhere. Please clarify via edits, not comments. [ask] "it is not giving me the expected results" For errors an [mre] should include the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) When you get a result that you don't expect, stop trying to find your overall goal & find out what your misunderstanding is.--Isolate the first unexpected subexpression & its input & output. (Debugging fundamental.) — philipxy, Feb 01 '20 at 00:12
This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly". When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, Feb 01 '20 at 00:18
[What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/q/4048151/3404097) [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) Seems like when this is clear it may be a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post, use one phrasing as title. — philipxy, Feb 01 '20 at 00:24

score 1 · Answer 1 · answered Jan 31 '20 at 12:17

1

I think that the logic you want is:

select u.surname, u.othernames, count(s.parent_subscription_id) referrals
from subscriptions s
left join subscriptions p on p.id = s.parent_subscription_id
inner join users u on u.id = coalesce(p.user_id, s.user_id)
group by u.id, u.surname, u.othernames

Demo on DB Fiddle:

surname | othernames | referrals
:------ | :--------- | --------:
Doe     | John       |         2
Doe     | Jane       |         1
Doe     | Mary       |         2

answered Jan 31 '20 at 12:17

GMB

216,147
25
84
135

This brought out the results almost as expected but the only issue I seem to be having with it is that it doesn't seem to capture all the users. I have 99 users but the result shows 45 records and so the records subscription referrals for some users are missing. I was expecting it to show 0 for those with no referrals. Can it be tweaked to do that? – Josh Jan 31 '20 at 12:53

score 1 · Answer 2 · answered Jan 31 '20 at 14:18

You ca use join between users table and select calculated count as:

SELECT 
    -- Get all users
    users.surname, 
    users.othernames,
    IFNULL(reff.cnt, 0) as referrals -- Preset 0 for users have not referrals in joined table
FROM users
-- Join calculation result
LEFT JOIN (
    -- Calculate count by user
    SELECT parent_subscription_id, COUNT(*) AS cnt
    FROM subscriptions
    GROUP BY subscriptions.parent_subscription_id
) reff on reff.parent_subscription_id = users.id;

This has been very helpful in guiding me to the right path. Applying IFNULL(count,0) has helped me see where I was getting it wrong. Thank you so much. +1 — Josh, Feb 02 '20 at 11:17

Fahmi · Answer 3 · 2020-01-31T11:43:08.570

0

Change group by fields with users.surname, users.othernames

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 users.surname, users.othernames

edited Jan 31 '20 at 11:43

answered Jan 31 '20 at 11:36

Fahmi

37,315
5
22
31

1

Use `count(s.parent_subscription_id)` instead of `count('s.parent_subscription_id')`. Otherwise it will be treated as string and will produce incorrect result (nulls will be included). – slaakso Jan 31 '20 at 11:39
I don't think this is giving the results the OP expects. It seems to me that the count of referrals should be based on the number of records where the user's subscription.id is used in the subscription.parent_subscription_id. For example, parent_subscription_id 1 is used in 2 records, the owner of that subscription.id is user 1 (John). – Moob Jan 31 '20 at 12:03

score 0 · Answer 4 · answered Jan 31 '20 at 12:15

you need to group by id of users table because you need get count for each user and here is your main table is users. try like this.

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 users.id

score 0 · Accepted Answer · answered Feb 02 '20 at 11: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

MYSQL GROUP BY LEFT JOIN and COUNT

5 Answers5