Jump to content


Photo

mysql count help


  • Please log in to reply
4 replies to this topic

#1 lilywong

lilywong
  • Members
  • PipPipPip
  • Advanced Member
  • 56 posts

Posted 03 August 2006 - 08:42 AM

i have a table data as below:

Item

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

#2 yarub

yarub
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts
  • LocationSioux City, IA

Posted 03 August 2006 - 08:50 AM

$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

#3 lilywong

lilywong
  • Members
  • PipPipPip
  • Advanced Member
  • 56 posts

Posted 03 August 2006 - 08:55 AM

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.

#4 manmadareddy

manmadareddy
  • Members
  • PipPipPip
  • Advanced Member
  • 50 posts

Posted 03 August 2006 - 08:58 AM

$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.


#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 August 2006 - 02:24 PM

You don't need the distinct, nor will it have any effect with the GROUP BY already in place.  DISTINCT is NOT a function!
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users