By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,640 Members | 1,600 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,640 IT Pros & Developers. It's quick & easy.

Count Number of Items in each Category

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
Barkster wrote:
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

It depends on your database - both which database and what version you're using.

Since this isn't a PHP question, try asking in a newsgroup for your database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Apr 10 '06 #2

P: n/a
Yeah so used to posting here since I'm building in php, anyway it is
mysql 4

Apr 10 '06 #3

P: n/a
Select
Category.CategoryID,
Category.Category,
Category.Sorting,
Count(Listings.CategoryID)
From
Category LEFT JOIN Listings on(Listings.CategoryID=category.CategoryID)

Order By
Category.Sorting Desc

Apr 10 '06 #4

P: n/a
I was hoping it was that simple but when I run that I get an error:
Mixing of a GROUP colums (min(), max(), Count()) with no GROUP columns
is illegal if there is no GROUP BY clause

I'm trying do display something like this, where number of items in
each category is in parenthesis.

Category1(25)
Category2(57)
Category3(240)

Apr 10 '06 #5

P: n/a
My fault,
at the end of select you need to add groub by Category.CategoryID
and you will get what you need.

Apr 10 '06 #6

P: n/a
My fault,
at the end of select you need to add "group by Category.CategoryID"
and you will get what you need.

Apr 10 '06 #7

P: n/a
Figured it out, was close to what you mentioned

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

Apr 10 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.