Count number of times data appears

[Kingskin]

Hi,

 

I'd like to be able to count the number of times the same data appears in a table. I know i could just use a standard COUNT to do it, but the trouble is I want to do it for every item in my table. An example. Say a column from my table called 'country' looks like this:

 

country:

 

Holland

England

Ireland

England

Italy

Holand

England

 

I want to return a list of entries, numbered by the amount of times they occur, like this:

 

England - 3

Holland - 2

Ireland - 1

Italy - 1

 

Can anyone help me with this please?

[Kingskin]

Never mind, sorted now. For anyone else who may turn ths thread up after a search, here's what I did:

 

$query = "SELECT location FROM episode";
$result = mysql_query($query);
while ( $row = mysql_fetch_array($result))
{
extract($row);
$locations[] = $location;
}
$output = array_count_values($locations);

[ryanlwh]

FYI..

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] country, COUNT(country) FROM episode GROUP BY country [!--sql2--][/div][!--sql3--]

[Kingskin]

FYI..

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] country, COUNT(country) FROM episode GROUP BY country [!--sql2--][/div][!--sql3--]

315422[/snapback]

 

Ah, Ok that looks like a better way of doing it, thanks