how to find out the rank of a row based on a mysql sum?

Question

Hi there,

I'm wanting to find the ranking / number of a row. I'm not sure if I'm explaining this well so I'll try.

I have the query

$sql = 'SELECT SUM(amount) AS total FROM sales ORDER BY total DESC';

$res = mysql_query($sql);

while($row = mysql_fetch_array($res)) {

// go through and print each row biggest 'total' first
echo $row['total'] . '<br />';

}

Now I want to go through and give each a ranking based on the biggest 'total' being number '1'.

So I can do that with php by doing some counting:

$sql = 'SELECT SUM(amount) AS total FROM sales ORDER BY total DESC';

$res = mysql_query($sql);

$rank = 1;

while($row = mysql_fetch_array($res)) {



// go through and print each row biggest 'total' first
echo 'rank: ' . $rank . ', ' . $row['total'] . '<br />';

$rank = $rank + 1;

}

This is good and working. But what I'm wanting to do, is be able to determine the ranking of a row without php so I can do an sql query based on say an affiliate ID from the sales table.

So for example I have 100 rows of sales data with an affiliate ID linked to each row, how would I go about simply getting the ranking based on the affiliate with the biggest total?

Answer 1

You can use a recursive variable to do this, like so:

select 
    @rownum:=@rownum+1 as rank,
    sum(amount) as total
from 
    sales,
    (select @rownum:=0) a
order by total desc

To grab the ranking of a given affiliate, you would have to do:

select
    a.*,
    t.rank,
    t.total
from
    affiliates a
    inner join (
        select 
            @rownum:=@rownum+1 as rank,
            affiliate_id,
            sum(amount) as total
        from 
            sales,
            (select @rownum:=0) r
        group by affiliate_id
        order by total desc) t on
       a.affiliate_id = t.affiliate_id
where
    a.affiliate_id = 342

Now that's (relatively) slow, because you have to do a table scan each time.

If you weren't using MySQL, I'd suggest a subquery, but by and large MySQL optimizes subqueries horrendously. It seems like it'd be fairly slow to me, but I haven't benchmarked it. At any rate, you can do this:

select
    a.*,
    (select
        count(*)+1
    from
        (select affiliate_id from sales 
         group by affiliate_id having sum(amount) >
            (select sum(amount) from sales where affiliate_id = a.affiliate_id)))
        as rank
from
    affiliates a
where
    a.affiliate_id = 342

Answer 2

Other implementations of DB has row_number and row_number_count. If you are using MySQL, I suggest you take a look at this walk-around