PHP - Count Grouped Results

Hi. I have the following code where I grab the names and average overall rating from my database and group by the name.

Code: [Select]
$sql="SELECT name, AVG(overall) AS overall FROM $tbl_name GROUP BY name ORDER BY AVG(overall) DESC";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){
Does anybody know how I can count the number of names that have been grouped together?

For instance:

Name     Overal ratingl
John       2
Pete       5
John       8

It should group the john's, calculate the average rating (8+2)/2=5, and then show that there have been 2 johns making up the 5 rating?

I've tried using the code below and messed around with it loads but cant get it to work:

Code: [Select]
$sql="SELECT name, AVG(overall) AS overall FROM $tbl_name COUNT name GROUP BY name ORDER BY AVG(overall) DESC";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){

echo $rows['Count name'];
Thanks in advance.

Hello all,

I've built a database of coupons. Each coupon is assigned to a category (so all electronics coupons display under electronics). On my category list I'd like to display how many coupons is in each category.

Currently this function displays my category list:
function fetch_categories() {
mysql_connect(SQL_HOST_NAME, SQL_USER_NAME, SQL_PASSWORD) or die(mysql_error());
mysql_select_db(SQL_DATABASE) or die(mysql_error());
$result = mysql_query("SELECT * FROM categories ORDER BY category_name")or die(mysql_error());

echo '<div id="category_list">';
echo '<ul class="cat_list">';

while($row=mysql_fetch_assoc($result)){

//get total of discounts in each category
$count_result = mysql_query("SELECT * FROM category_relations, discounts WHERE category_relations.member_of = '$row[id]' AND discounts.active = '1'")or die(mysql_error());
$num_rows = mysql_num_rows($count_result);
echo '<span class="numrows">('.$num_rows.')</span>';

echo '<li><a href="index.php?cat_ID='.$row['id'].'">'.$row['category_name'].'  </a></li>';
}

echo '</ul>';
echo '</div>';
}

The above is a debug version so it's not all formatted correctly.

However right now, that exports "3" for Airlines when right now, I have only one discount in the entire database.

This is my category_relations table:
Code: [Select]
--
-- Table structure for table `category_relations`
--

CREATE TABLE IF NOT EXISTS `category_relations` (
  `id` int(10) NOT NULL,
  `member_of` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `category_relations`
--

INSERT INTO `category_relations` (`id`, `member_of`) VALUES
(2, 2),
(1, 1),
(3, 1),
(4, 1);

And this is my discounts table:
Code: [Select]
--
-- Table structure for table `discounts`
--

CREATE TABLE IF NOT EXISTS `discounts` (
  `id` smallint(5) NOT NULL AUTO_INCREMENT,
  `redeem` tinyint(3) NOT NULL,
  `discount_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `short_description` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `eligibility` blob NOT NULL,
  `url` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `coupon_code` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `long_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `business_name` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(80) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_street` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_city` varchar(75) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_state` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_location_zip` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `categories` blob NOT NULL,
  `level` tinyint(5) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `user_id` varchar(2555) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `level` (`level`,`active`),
  KEY `user_id` (`user_id`(333))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `discounts`
--

INSERT INTO `discounts` (`id`, `redeem`, `discount_title`, `short_description`, `eligibility`, `url`, `coupon_code`, `start_date`, `end_date`, `long_description`, `logo`, `business_name`, `phone`, `store_location_street`, `store_location_city`, `store_location_state`, `store_location_zip`, `categories`, `level`, `active`, `user_id`) VALUES
(1, 0, '$2.00 Off Transportation', '', 0x4172726179, 'www.baysideshuttle.com', '', '2010-01-01 00:00:00', '2011-01-01 00:00:00', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer varius ipsum id justo pellentesque laoreet. Curabitur tempus tincidunt...', NULL, 'Bayside Airport Shuttle', '850-123-3456', NULL, NULL, NULL, NULL, 0x4172726179, 0, 1, '24');

It seems to be completely ignoring my active check. How can I fix this, or is there a better way to do this query?

I'm getting the number of friends in each STATE. How do I split the results in $returnS?

$query = "SELECT statez, COUNT(statez)FROM abs GROUP BY statez"; 
$results = mysql_query($query);
$returnS="";
while($line = mysql_fetch_array($results))
{
$returnS.= $line['COUNT(statez)'].",,".$line['statez'].",,,";
}
echo $returnS;
mysql_close($link);
?>

First up quick thanks in advance, any and all help is greatfully recieved I have this query:

SELECT Player.MembershipNo , Player.FirstName , Player.LastName
, Venue.VenueName as Venue, Results.MemCard, Results.EarlyReg
, Position.Points as Venue_Points, Results.Date
FROM Position , Player , Results , Venue 
WHERE Player.MembershipNo =Results.MembershipNo 
AND Results.Position =Position.Position 
AND Venue.VenueID =Results.VenueID 
AND Results.Date BETWEEN '2014-07-01' AND '2014-09-30' 
ORDER BY MembershipNo, Venue

which returns these results:

+--------------+-----------+----------+-------------------+---------+----------+--------------+------------+
| MembershipNo | FirstName | LastName |       Venue       | MemCard | EarlyReg | Venue_Points |    Date    |
+--------------+-----------+----------+-------------------+---------+----------+--------------+------------+
|            0 | Bob       | Stevens  | The Dolphin       |       1 |        1 |           32 | 27/08/2014 |
|            0 | Bob       | Stevens  | The Enigma Tavern |       1 |        1 |           40 | 08/07/2014 |
|            0 | Bob       | Stevens  | The Enigma Tavern |       1 |        1 |           16 | 15/07/2014 |
|            1 | Dave      | Green    | The Dolphin       |       1 |        1 |           20 | 13/08/2014 |
|            1 | Dave      | Green    | The Dolphin       |       1 |        1 |            2 | 20/08/2014 |
+--------------+-----------+----------+-------------------+---------+----------+--------------+------------+

I'm trying to make a function which will display the top X results of the row Y which I set, for this instance I'm using the row browser and the top 5 results from my table statistics, the where is just to eliminate Search Bot results from showing up. I also want it to return the count of the amount of rows as well. So say there was 10 results for the browser 'Safari', then it would return the count of 10 for that result as well as the result itself.

Code: [Select]
$display->show_list('statistics', 'browser', '5', 'WHERE browser!=\'Search Bot\'');
Here is my function. I'm cleaned it up a bit to remove certain checks and outputs if the query were to fail, etc.

Code: [Select]
function show_list($table, $row, $limit = 5, $where = NULL) {

$item = mysql_query("SELECT DISTINCT $row FROM $table $where LIMIT $limit");

            $result = array();

while ($fetch = mysql_fetch_array($item)) {

            $result[] = $fetch;
}
            return $result;
}
Basically, how would I go about making it count the amount of rows for the row I've set, and then to output that along side the result?