MySQl : How do I use Count on a Sub-Query?

Question

I've got a MySQL statement that selects a name and also makes a ranking.

  SELECT t.name,                        
         (SELECT COUNT(*)
            FROM my_table1 z
           WHERE z.type LIKE '%Blue%' 
             AND t.type  LIKE '%Blue%'
             AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank                     
    FROM my_table1 t, my_table2 d
   WHERE d.name = t.name
     AND t.status != 'unknown'
     AND t.type = 'Blue'
     AND d.area_served = '$area_id'                 
ORDER BY rank ASC

But, I also need to know out of how many the rank is calculated. So for example, ranked #4 out of X.

How do I count the total number of rows in the ranking sub-query? I need the count for this bit:

(SELECT COUNT(*)
    FROM my_table1 z
    WHERE z.type LIKE '%Blue%' AND  t.type  LIKE '%Blue%'
    AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank

Thank you.

-Laxmidi

Answer 1

I'm not sure if I understand, but I think you should include FOUND_ROWS() in the subquery.

(SELECT COUNT(*), FOUND_ROWS() FROM my_table1 z WHERE z.type LIKE '%Blue%' AND t.type LIKE '%Blue%' AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS rank, number

You can find more information here: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Answer 2

I'm having a little trouble understanding your question, but I'll take a stab at it.

This part gets you the specific rank number (such as 4):

(SELECT COUNT(*)
    FROM my_table1 z
    WHERE z.type LIKE '%Blue%' AND  t.type  LIKE '%Blue%'
    AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank

So in order to find the total number of rows from that subquery, you should just need to remove your WHERE clause. I'm not sure if you need to remove everything in the WHERE clause though, maybe just the types, or just the scores?

If you have multiple rows that you want to be grouped together, I would use GROUP BY and then use COUNT as necessary.

Answer 3

You can add one more subquery - it will be the same as the existing, but without AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4) condition:

 SELECT t.name,                        
     (SELECT COUNT(*)
        FROM my_table1 z
       WHERE z.type LIKE '%Blue%' 
         AND t.type  LIKE '%Blue%'
         AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank,
      // new subquery
    (SELECT COUNT(*)
        FROM my_table1 z
       WHERE z.type LIKE '%Blue%' 
         AND t.type  LIKE '%Blue%') as max_rank
FROM my_table1 t, my_table2 d   
WHERE d.name = t.name
 AND t.status != 'unknown'
 AND t.type = 'Blue'
 AND d.area_served = '$area_id'                 
ORDER BY rank ASC

Answer 4

You can use the same subselect without the score comparison:

SELECT t.name,                        
         (SELECT COUNT(*)
            FROM my_table1 z
           WHERE z.type LIKE '%Blue%' 
             AND t.type  LIKE '%Blue%'
             AND (z.score1+ z.score2 + z.score3 + z.score4) >= (t.score1+ t.score2 + t.score3 + t.score4)) AS  rank,                 
         (SELECT COUNT(*)
            FROM my_table1 z
           WHERE z.type LIKE '%Blue%' 
             AND t.type  LIKE '%Blue%') AS rankOutOf
    FROM my_table1 t, my_table2 d
   WHERE d.name = t.name
     AND t.status != 'unknown'
     AND t.type = 'Blue'
     AND d.area_served = '$area_id'   

The rankOutOf column returns the number of candidates considered in the ranking query.