Jump to content


Photo

Count Items in each Category


  • Please log in to reply
4 replies to this topic

#1 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 09 April 2006 - 04:30 AM

I'm trying to list my categories and how many items are in each category. I'm not much on sub queries. How can I combine these two items. I'd like to display the category and the corresponding number of items in each

Cats:
Select
Category.CategoryID,
Category.Category,
Category.Sorting
From
Category
Order By
Category.Sorting Desc

Items:
Select Count(Listings.CategoryID)
From
Listings
Group By
Listings.CategoryID

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:49 PM

This should work (UNTESTED):

Select
Category.CategoryID,
Category.Category,
Category.Sorting,
(Select Count(CategoryID)
From
Listings WHERE CategoryID = Category.CategoryID) AS cnt
From
Category
Order By
Category.Sorting Desc

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 10 April 2006 - 02:54 AM

Thanks for the help, I messed with it for a while and can't get it to work. i tried the following

Select
Category.CategoryID,
Category.Category,
Category.Sorting,
(Select Count(Listings.CategoryID)
From
Listings WHERE Listings.CategoryID = Category.CategoryID) AS cnt
From
Category
Order By
Category.Sorting Desc

&

Select
Category.CategoryID,
Category.Category,
Category.Sorting,
(Select Count(Listings.CategoryID) as cnt
From
Listings WHERE Listings.CategoryID = Category.CategoryID)
From
Category
Order By
Category.Sorting Desc

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 April 2006 - 03:14 PM

There's nothing wrong with the query I provided, but it does require MySQL 4.1+ for subselects to work.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 10 April 2006 - 03:50 PM

Ah, 4.0.25 glad you mentioned it, just moved to a new host and was supposed to have 4.1. Now I know why I couldn't get any sub selects to work. I did though get it to work like this

Select
Category.CategoryID,
Category.Category,
Category.Sorting,
Count(Listings.CategoryID) as cnt
From
Category LEFT JOIN Listings on(Listings.CategoryID=Category.CategoryID)
Group By
Category.CategoryID
Order By
Category.Sorting Asc


Thanks for the help.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users