I am developing a project for a client with a single table in the database for users which includes information about each user, including a field for the userID of a user who referred them.
What I need to do is query the "referrer" field of each user record and count the number of times each "referrer" comes up - then output that referrer's name and info along with the count of referrals.
Simplified example of table:
FNAME LNAME USERID REFERRER
--------- -------- ------------- --------------
Jane Doe JaneDoe1
John Doe JohnDoe2 JaneDoe1
Cary Grant CaryGrant3 JaneDoe1
Mary Smith MarySmith4 JohnDoe2
Example of results I need to see:
FNAME LNAME USERID REFERRALS
-------- -------- --------------- -------------
Jane Doe JaneDoe1 2
John Doe JohnDoe2 1
This is the code I am currently using:
"SELECT t1.fname, t1.lname, t1.userID, COALESCE(t2.referrals, 0) AS referrals FROM users as t1 LEFT OUTER JOIN (SELECT referrer, COUNT(*) AS referrals FROM users GROUP BY userID) AS t2 ON t1.userID = t2.referrer WHERE referrals > 0 "
The results I actually get like this:
FNAME LNAME USERID REFERRALS
-------- -------- --------------- -------------
Jane Doe JaneDoe1 1
Jane Doe JaneDoe1 1
John Doe JohnDoe2 1
So, each time a user was a referrer, it generates a row for them with a count of 1 referral, rather than outputting a single row with a count of however many referrals it should be.
Is there something missing that I can add to my query to make it generate in the manner I am looking for, or should I just create a second table in my database to store the referral information so I can cross reference the referrals table with the users table?
Thanks!