Count and Group By Query Help

[factoring2117]

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?

[avvllvva]

Your query seems to be okay.  then where u r facing problem ?? whats ur output ?

[factoring2117]

I need to find the percent who have converted. So I have grouped according to referral and ordered by total number of users and now I need to add the number who have converted and then divide by total number of prospects for that keyword.

 

1) Group keywords and number of prospects- Completed

2) Add number of conversions per keyword group- Need syntax

3) Complete math to make into percentage- Can do by php after retrieval

 

Thanks

[avvllvva]

This may give result as u wish,

SELECT COUNT(*) as count,v.referral,
(SELECT  count(*) as count2 FROM visitors WHERE conversion = 1 AND referral = v.referral) as num_of_conversions
FROM visitors v GROUP BY v.referral ORDER BY count DESC

<?php

while($row = mysql_fetch_array($select)) {
$percentage = floor(($row['num_of_conversions']/$row['count'])*100);

  $table .= '<tr><td>'.substr($row['referral'],0,35).'</td><td>'.$row['count'].'</td><td>'.$percentage.'%</td></tr>';
}

?>