PHP count group by Problem

[factoring]

The issue that I am running into now is that I need to count multiple rows and display them.

Here is what my table looks like.

id   referral        conversion
1   annuity     0
2   annuity         1
3   annuities        1
4   annuities        1
5   annuity          0
6   settlement      0
7   settlement      0

I want my table to look like this:

keyword     Number of Prospects        Conversion %
annuity       3                                  33%
annuities     2                                   100%
settlement   2                                  0%

Here is the code that I have so far:

$table = '<table>';
$table .= '<tr><th class="sortable">Keyword</th><th class="sortable">Number of Prospects</th><th>Conversion</th></tr>';


$select = mysql_query("select count(*) as count,referral from visitors group by referral order by count") or die(mysql_error());


while($row = mysql_fetch_array($select)) {
  $table .= '<tr><td>'.substr($row['referral'],0,35).'</td><td>'.$row['count'].'</td><td>'.$row['short'].'</td></tr>';
}
$table .= '</table>';

Does anyone have any suggestions?

[Shrike]

You can get the SQL output you want with a simple query, assuming conversion is always 0 or 1.

SELECT referral, COUNT( id ) AS count, ( AVG( conversion ) / COUNT( id )) %100 AS conversionrate
FROM visitors
GROUP BY referral
ORDER BY count