Mysql counting query:

[PoorDoggie]

I currently have this mysql query:

SELECT c.*, s.* FROM categories c, subcategories s WHERE c.cat_id=s.cat_id GROUP BY c.cat_id ORDER BY c.cat_name ASC

Now I also want to count the amount of subcategories in a category (so that I can divide it by 3 for layout reasons! :D). in the table "subcategories" I have "cat_id" which is linked to the categories table, "subcat_id" which isn't really of any use in this script, and "subcat_name". in the "categories" table I have "cat_name" and I have "cat_id".

How would I go about doing it?

Thanks
Tom

 

[andysyme]

Not feeling very SQLey tonight, but is this anything close to what you want?

SELECT c.cat_name, COUNT(s.subcat_name)
FROM category c, subcategory s
WHERE c.cat_id = s.cat_id
GROUP BY c.cat_name

Andy

 

[PoorDoggie]

no... but don't worry - i can do it another way.

Its my egotistic coding style where I want to cram as much as i can into the smallest amount of space! hehe... its ok, ill split it all up a bit! :D hehe... thanks

 

[andysyme]

Just tried this, it will give you a list of cats and the number of subcats in each.

Guess I didn't understand the question!!

Andy

 

[PoorDoggie]

ok... i will try it then I am sure i had that before

 

[Ray]

no... but don't worry - i can do it another way.

Its my egotistic coding style where I want to cram as much as i can into the smallest amount of space! hehe... its ok, ill split it all up a bit! :D hehe... thanks

heh... thats how i code its the best way... less space, less bandwidth...

 

[PoorDoggie]

heh... thats how i code its the best way... less space, less bandwidth...

generally quicker scripts too! :D lol