Page 1 of 1

Mysql Count Help!

Posted: Tue May 22, 2007 7:40 pm
by bibsta
Hi There,

Okay here goes what i have been trying to do is its really hard to explain so i will try and draw below:

Table1----------------------------- Table2
cat_id ----------------------------- review_id
name

This is how its displayed:
cat_id----------------------------- name
1 ----------------------------- Testing

Okay so what im trying to do is count hows many reivew ids in table are the same and cat_id in table1 and then display the results like this:
cat_id ----------------------------- name ----------------------------- result
1 ----------------------------- Testing ----------------------------- 23

Sorry if this sounds confusing just think about how i feel lol :)

Laymans Terms: count how many reviews are in each categorys

Posted: Tue May 22, 2007 7:52 pm
by feyd
You're looking to use COUNT() and a GROUP BY clause.

Posted: Tue May 22, 2007 8:28 pm
by JunkCodeNet
I setup a database to try this out and came up with the following:

Code: Select all

SELECT `categories`.`name` , COUNT( `RID` )
FROM `categories`
LEFT JOIN `reviews` ON ( `reviews`.`CID` = `categories`.`CID` )
GROUP BY `reviews`.`CID`
Which gave me the following results:

Code: Select all

mysql> SELECT `categories`.`name` , COUNT( `RID` )
    -> FROM `categories`
    -> LEFT JOIN `reviews` ON ( `reviews`.`CID` = `categories`.`CID` )
    -> GROUP BY `reviews`.`CID`;
+-----------+----------------+
| name      | COUNT( `RID` ) |
+-----------+----------------+
| Cars      |              2 |
| Computers |              3 |
| Other     |              1 |
+-----------+----------------+
3 rows in set (0.00 sec)
The database I setup to run this code on is:

Code: Select all

CREATE TABLE `categories` (
  `CID` int(11) NOT NULL auto_increment COMMENT 'category ID.',
  `Name` varchar(255) collate latin1_general_ci NOT NULL COMMENT 'category Name.',
  PRIMARY KEY  (`CID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='categories table.' AUTO_INCREMENT=4 ;

INSERT INTO `categories` VALUES (1, 'Cars');
INSERT INTO `categories` VALUES (2, 'Computers');
INSERT INTO `categories` VALUES (3, 'Other');

CREATE TABLE `reviews` (
  `RID` int(11) NOT NULL auto_increment COMMENT 'Review ID.',
  `CID` int(11) NOT NULL COMMENT 'Category ID.',
  `Name` varchar(255) collate latin1_general_ci NOT NULL COMMENT 'Review Name.',
  `Content` text collate latin1_general_ci NOT NULL COMMENT 'Review Content.',
  PRIMARY KEY  (`RID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='reviews table.' AUTO_INCREMENT=7 ;

INSERT INTO `reviews` VALUES (1, 1, 'Car Review', 'This is a car review');
INSERT INTO `reviews` VALUES (2, 2, 'Computer Review', 'This is a computer review.');
INSERT INTO `reviews` VALUES (3, 1, 'Another car review.', 'this is another car review.');
INSERT INTO `reviews` VALUES (4, 3, 'Chip shop review', 'This is a review about a local chip shop.');
INSERT INTO `reviews` VALUES (5, 2, 'Some computer review.', 'another computer review.');
INSERT INTO `reviews` VALUES (6, 2, 'motherboard review.', 'a review about a motherboard');
Hope this helps.
- Dave.