Count Number of 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.Catego ryID,
Category.Catego ry,
Category.Sortin g
From
Category
Order By
Category.Sortin g Desc

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

I've tried the following but can't get it to work:

Select
Category.Catego ryID,
Category.Catego ry,
Category.Sortin g,
(Select Count(Listings. CategoryID)
From
Listings WHERE Listings.Catego ryID = Category.Catego ryID) AS cnt
From
Category
Order By
Category.Sortin g Desc

[Jerry Stuckle]

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.Catego ryID,
Category.Catego ry,
Category.Sortin g
From
Category
Order By
Category.Sortin g Desc

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

I've tried the following but can't get it to work:

Select
Category.Catego ryID,
Category.Catego ry,
Category.Sortin g,
(Select Count(Listings. CategoryID)
From
Listings WHERE Listings.Catego ryID = Category.Catego ryID) AS cnt
From
Category
Order By
Category.Sortin g 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*******@attgl obal.net
=============== ===

[Barkster]

Yeah so used to posting here since I'm building in php, anyway it is
mysql 4

[Ac1d^]

Select
Category.Catego ryID,
Category.Catego ry,
Category.Sortin g,
Count(Listings. CategoryID)
From
Category LEFT JOIN Listings on(Listings.Cat egoryID=categor y.CategoryID)

Order By
Category.Sortin g Desc

[Barkster]

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)

[Ac1d^]

My fault,
at the end of select you need to add groub by Category.Catego ryID
and you will get what you need.

[Ac1d^]

My fault,
at the end of select you need to add "group by Category.Catego ryID"
and you will get what you need.

[Barkster]

Figured it out, was close to what you mentioned

Select
Category.Catego ryID,
Category.Catego ry,
Category.Sortin g,
Count(Listings. CategoryID) as cnt
From
Category LEFT JOIN Listings on(Listings.Cat egoryID=Categor y.CategoryID)
Group By
Category.Catego ryID
Order By
Category.Sortin g Asc