0

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!

Edited by Ezzaral: Added some additional code tags so the tables retained their format.

2
Contributors
1
Reply
8
Views
5 Years
Discussion Span
Last Post by cereal
0

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.

Edited by cereal: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.