MYSQL: Select and order users table by matching values, but separate column names?-CodePudding

I'm trying to check and order my users table who has the most origins (referals).

I have a users table, which has an 'origin' column, filled in with another users 'username' column if they refereed them, null if nobody.

Here is what I have so far, but its constantly returning 0 for the origin_count column?

SELECT username, 
    (SELECT COUNT(*) 
     FROM users 
     WHERE origin = username) AS origin_count 
FROM users 
WHERE tmp_allow_share = 1 AND approved_at IS NOT NULL;

CodePudding user response：

SELECT COUNT(*) FROM users WHERE origin = username counts how many users have themselves as origin.

What you want to do is group by username and count origin (possibly distinct if you're looking for unique referals).

SELECT COUNT(origin) FROM users GROUP BY username;

CodePudding user response：

You don't need a subquery to calculate origin count, rather use count directly with group by

SELECT username,
   count(*) AS origin_count
FROM users
WHERE tmp_allow_share = 1
  AND approved_at IS NOT NULL
GROUP BY username;