Mysql counting query:

PoorDoggie · 10-14-2006, 11:38 AM

I currently have this mysql query:

Code:

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!

). 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".
????: NamePros.com http://www.namepros.com/programming/247220-mysql-counting-query.html

How would I go about doing it?

Thanks
Tom

andysyme · 10-14-2006, 12:57 PM

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 · 10-14-2006, 01:45 PM

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!

hehe... thanks

andysyme · 10-14-2006, 01:59 PM

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 · 10-14-2006, 02:10 PM

ok... i will try it then

I am sure i had that before

-Ray- · 10-16-2006, 10:42 AM

Originally Posted by 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!

hehe... thanks

heh... thats how i code

its the best way... less space, less bandwidth...

PoorDoggie · 10-16-2006, 01:19 PM

Originally Posted by templaterave

heh... thats how i code

its the best way... less space, less bandwidth...

generally quicker scripts too!

lol

10-14-2006, 11:38 AM	THREAD STARTER #1 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	Mysql counting query: I currently have this mysql query: Code: 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! ). 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". ????: NamePros.com http://www.namepros.com/programming/247220-mysql-counting-query.html How would I go about doing it? Thanks Tom

10-14-2006, 12:57 PM	#2 (permalink)
andysyme NamePros Member Join Date: Sep 2006 Posts: 134	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

10-14-2006, 01:45 PM	THREAD STARTER #3 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	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! hehe... thanks

10-14-2006, 01:59 PM	#4 (permalink)
andysyme NamePros Member Join Date: Sep 2006 Posts: 134	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

10-14-2006, 02:10 PM	THREAD STARTER #5 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,408	ok... i will try it then I am sure i had that before

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)