mysql count help

[lilywong]

i have a table data as below:
[b]
Item[/b]
pepsi
pepsi
orange
milo
orange
milo
milo
orange
pepsi
pepsi
pepsi

i want to count and get the maximum data, which is pepsi in this case, how should i do in mysql

[yarub]

$i=0;
$query = mysql_query("SELECT COUNT(Item) FROM tablename WHERE Item='Pepsi'");
while($row = mysql_fetch_array($query)) {
$i++;
echo("Total Pepsi: ".$row['COUNT(Item)']."");
}

Would appear as...

Total Pepsi: 5

[lilywong]

i wish to get the result as pepsi, for example, i want to select the top selling drink in the shop, so i need to query to return me pepsi, coz pepsi appear 5 times. thanks.

[manmadareddy]

$sql="select distinct(Item),count(Item) as cnt,itemcode from products group by Item";
By using the above query u will get the counts of each item with itemcode.
u can do sort on the result set and get the max one.

[fenway]

You don't need the distinct, nor will it have any effect with the GROUP BY already in place.  DISTINCT is NOT a function!