Count Items in each Category

[barkster]

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

[fenway]

This should work (UNTESTED):

[code]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[/code]

[barkster]

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

[fenway]

There's nothing wrong with the query I provided, but it does require MySQL 4.1+ for subselects to work.

[barkster]

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.