Mysql counting query:

PoorDoggie · 10-14-2006, 10: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".

How would I go about doing it?

Thanks
Tom

andysyme · 10-14-2006, 11:57 AM

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, 12: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, 12: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, 01:10 PM

ok... i will try it then

I am sure i had that before

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

Quote:

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, 12:19 PM

Quote:

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, 10:38 AM	#1 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	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". How would I go about doing it? Thanks Tom __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more!

10-14-2006, 11:57 AM	#2 (permalink)
andysyme NamePros Member Join Date: Sep 2006 Posts: 132 0.00 NP$ (Donate)	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 __________________ Coming soon - POCHost.com

10-14-2006, 12:45 PM	#3 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	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 __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more!

10-14-2006, 12:59 PM	#4 (permalink)
andysyme NamePros Member Join Date: Sep 2006 Posts: 132 0.00 NP$ (Donate)	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 __________________ Coming soon - POCHost.com

10-14-2006, 01:10 PM	#5 (permalink)
PoorDoggie Soon to be RICHdoggie! Join Date: Jan 2005 Location: UK Posts: 2,390 316.50 NP$ (Donate)	ok... i will try it then I am sure i had that before __________________ █ Exchange your long URL for a much smaller one! █ Free Anonymizer/Proxy █ Taz Martin's blog - Learn all about internet marketing, promoting your website and much more!

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