469,902 Members | 1,944 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

Count Number of Items in each Category

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
I've tried the following but can't get it to work:
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

Apr 10 '06 #1
1 4033
Select
Category.CategoryID,
Category.Category,
Category.Sorting,
Count(Listings.CategoryID) as cnt
From Category, Listing

WHERE Listings.CategoryID = Category.CategoryID)
group by
Category.CategoryID,
Category.Category,
Category.Sorting
Order By
Category.Sorting Desc

If you are going to continue doing SQL - a good reference book would be
in order. Also - the term (in this case) that you would use for Google
would be Joins, Cross Joins

Apr 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by knoak | last post: by
5 posts views Thread by Mark | last post: by
2 posts views Thread by Chad A. Beckner | last post: by
10 posts views Thread by Marina | last post: by
7 posts views Thread by Barkster | last post: by
3 posts views Thread by acorn71 | last post: by
2 posts views Thread by googletired | last post: by
2 posts views Thread by winzone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.