Count users with X

Discussion in 'PHP' started by Yonder, Sep 2, 2011.

0
  1. #1
    So I have a sql table. I want to list the members in order. But I want to order them by the amount of people they have referred.

    I have a column when a user signs up the id of the referrer is added to this column.

    So count the refer ids then order them by who has the most referrals.

    Im a little confused how to do it someone could point me in the right direction that would be great.

    Thank you.
     
    Solved! View solution.
    Yonder, Sep 2, 2011 IP
  2. #2
    try something like this

    select TableA.id, count(TableB.id)
    from mytable as TableA, mytable as TableB
    where TableA.id = TableB.referrer_id
    group by TableA.id
    order by TableA.id

    Once you have that working you can look at adding more fields from TableA to the results.

    All we are doing is treating your table as two separate instances. MySQL lets us do that.
     
    sarahk, Sep 2, 2011 IP
  3. Yonder

    Yonder Peon

    Messages:
    12
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Awesome, thank you.
     
    Last edited: Sep 2, 2011
    Yonder, Sep 2, 2011 IP