MySQL: Count rows in another table

Hi,
Basically, I'm trying to display a list of categories, with the total number of people in each category (who have a name specified.)

For instance:

Categories
id | name
1 | Boys
2 | Girls
3 | Other


People
id | cat | name
1 | 2 | Sue
2 | 1 | Bob
3 | 1 |
4 | 3 | Rita

I would like the following result:

catname | members
1 | 1
2 | 1
3 | 1

Thanks!

 

Then you only need table people
SELECT cat AS catname, COUNT(cat) AS members FROM people WHERE name NOT NULL OR name NOT "" GROUP BY cat ORDER BY cat;
That should do the trick, I did not know how you define an non existant name either by NULL or en Empty String so I included both.

 

Thanks, but I do need the category table. This is a simplified example by the way. There are many fields in the 'category' table I need.

For the purpose of this example, say I need:

catname | members
Boys | 1
Girls | 1
Other | 1

Thanks and sorry for the mistake

 

Got it. You need something like this:

SELECT c.name catname, count(p.id) persons 
FROM people p 
LEFT JOIN categories c 
ON c.id = p.cat
GROUP BY p.cat
ORDER BY c.name 

PHP:

 

I have an aversion to the LEFT JOIN syntax, having been brought up in Oracle-land, so I would probably write

SELECT Categories.name, count (People.id)
FROM People, Categories
WHERE Categories.id = People.cat

But... what I really came here to ask is -- what is Rita if she's neither a boy nor a girl? I would have thought that was a classic boolean data type.

 

thanks guys, will try this out when im back in work monday.

I used a kinda tongue-in-cheek example. The real code is much less interesting!

Thanks again for the code!

 

This code is almost working.
How can I retrieve the total count of people in the category, and also the count of people where their name is not null?

Thanks!

 

I think it is working now, thanks!