Self Join with Count in Mysql

Question

I have a table that looks like this

ID     fname      lname      sponsor_id
1       Joe       Smith         0
2      John       Jones         1
3       Sue       Wills         1
4       Bob       Hass          3

I want to list all the leaders like this in descending order

ID   fname       lname      number_sponsored
1     Joe        Smith            2
3     Sue        Wills            1
2     John       Jones            0
4     Bob         Hass            0

I cannot for the life of me figure out this join. Any one want to take a stab at this?

Answer 1

You can count the number sponsored in sub query

 Select Id, fname, lname, 
( select count(sponser_id) from    table1 a where a.sponser_id = b.id) as number_sponsored
From table1 b
Order by number_sponsored desc

Answer 2

I think the only trick here is that you need a left join to get the rows with zero count. Here is one way to write the query:

select t.id, t.fname, t.lname, count(t2.sponsor_id) as num_sponsored
from table t left join
     table t2
     on t.id = t2.sponsor_id
group by t.id, t.fname, t.lname;

An alternative approach doesn't use an explicit join:

select t.*, (select count(*) from table t2 where t.id = t2.sponsor_id) as num_sponsored
from table t;

This may be clearer for those a bit confused by the question.

Answer 3

SELECT DISTINCT 
      id 
FROM
   tbl t
Left JOIN 
   (SELECT 
       number_sponsored,
       COUNT(DISTINCT id) AS number_sponsored 
   FROM
       tbl2
   GROUP BY   sponsor_id) AS number_sponsored 
   ON t.id = number_sponsored.id 
  Where t.sponsor_id >t2. number_sponsored 

or you can take a look at this resources

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

Reference: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html