Advanced PHP/MySQL Query

[jesyka82]

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!

[cereal]

This should work: GROUP BY referrer not by userID, so:

"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 referrer) AS t2 ON t1.userID = t2.referrer WHERE referrals > 0 "

userID is always one, and it should be a unique key field, so you get always 1, but the referrer no.