Getting Expected Results From 'count()'

[mjsulliv]

Hey all. Ive been trying to write what should be a simple query. I want to count the number of items 'M' in each of the containers 'C'The query I run is :

SQL query: SELECT C_ID, Count(C_ID) FROM `m` JOIN `c` Where C_ID = Cage_ID;

What I get is a result w/ the first C_ID and a sum of the number of items in all the containers. What I expected and want, is a result w/ three rows each w/ one C_ID and the number of M_IDs in them. Below is the SQL to creat a set of test tables. Any help would be appreciated. --- Mike

CREATE TABLE IF NOT EXISTS `c` (  `C_ID` int(11) NOT NULL,  `Room` varchar(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `c` (`C_ID`, `Room`) VALUES(1, 'A'),(2, 'A'),(3, 'B');CREATE TABLE IF NOT EXISTS `m` (  `M_ID` varchar(10) NOT NULL,  `Cage_ID` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;INSERT INTO `m` (`M_ID`, `Cage_ID`) VALUES('1', 1),('2', 1),('3', 1),('4', 2),('5', 2),('6', 2),('7', 2),('8', 3);

[justsomeguy]

You need to use GROUP BY to tell it how to do the count for each item.

[mjsulliv]

Indeed! Thank You!