MySql Count() ----How To

CString sSql;
sSql.Format("SELECT COUNT(*) as mycount FROM Series WHERE Name = 'jim'") ;

recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);

long count = recordset.GetFieldValue("mycount").lVal;

Now the variable count stores how many matches were made with the criteria you supply as an integer(how many records have jim in the field "name").


The above query works perfectly fine with MS Access. I am not sure what database you have. I hope the above example helps you to move forward in your program. You can use this code and improvise it to work with your database and code.

I found the following query while i was searching on google. Hope this helps you: -

SQL / MySQL » Select Clause » Count	 	
	
COUNT command with condition

/*
mysql>  SELECT species, sex, COUNT(*) FROM Bird
    ->         WHERE species = 'dog' OR species = 'cat'
    ->         GROUP BY species, sex;
Empty set (0.00 sec)


*/  
Drop table Bird;

CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',1998-01-30);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1998-01-30',NULL);

/* You need not retrieve an entire table when you use COUNT(). 
For example, the previous query, when performed just on dogs and 
cats, looks like this:
*/
 SELECT species, sex, COUNT(*) FROM Bird
        WHERE species = 'dog' OR species = 'cat'
        GROUP BY species, sex;

This code should really help you. If you don't need the group by just remove it and instead of having two conditions, just keep on condition and I am sure this will help you in your code.

Let me know if any of the codes help you.

--
AJ