SQL Query using COUNT

[Mark]

Hi, I have a Access database with two tables; a category table and an images
table. In the Category table I have a field for Index number and a field for
Categories. The images table has a field for all the images and a field to
specify what category the images are assigned to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of the
number of images assigned to each category from my main table of images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.

[Aaron [SQL Server MVP]]

SELECT Categories.Name,
COUNT(Images.CategoryID)
FROM Categories INNER JOIN Images
ON Images.CategoryID = Categories.CategoryID
GROUP BY Categories.Name

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Mark" <[e-mail]> wrote in message
news:Rm******************@stones.force9.net...

Hi, I have a Access database with two tables; a category table and an images table. In the Category table I have a field for Index number and a field for Categories. The images table has a field for all the images and a field to
specify what category the images are assigned to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of the
number of images assigned to each category from my main table of images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.

[Bob Barrows [MVP]]

Mark wrote:

Hi, I have a Access database with two tables; a category table and an
images table. In the Category table I have a field for Index number
and a field for Categories. The images table has a field for all the
images and a field to specify what category the images are assigned
to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of
the number of images assigned to each category from my main table of
images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.

SELECT c.Categories, count(*)
FROM Category c inner join Images i
ON c.Categories = i.Categories
GROUP BY c.Categories

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mark]

Thanks Aaron and Bob, its working now!

"Mark" <[e-mail]> wrote in message
news:Rm******************@stones.force9.net...

Hi, I have a Access database with two tables; a category table and an images table. In the Category table I have a field for Index number and a field for Categories. The images table has a field for all the images and a field to
specify what category the images are assigned to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of the
number of images assigned to each category from my main table of images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.

[Mark]

Hi, I have one other quick question: if I wanted to include the ID field for
each category, would it just be a case of changing the SQL from:

SELECT Categories.Name,
COUNT(Images.CategoryID)
FROM Categories INNER JOIN Images
ON Images.CategoryID = Categories.CategoryID
GROUP BY Categories.Name

TO:

SELECT Categories.ID, Categories.Name,
COUNT(Images.CategoryID)
FROM Categories INNER JOIN Images
ON Images.CategoryID = Categories.CategoryID
GROUP BY Categories.Name

So far this has not worked and I get the error:

"You tried to execute a query that does not include the specified expression
'ID' as part of an aggregate function"

"Mark" <[e-mail]> wrote in message
news:Rm******************@stones.force9.net...

Hi, I have a Access database with two tables; a category table and an images table. In the Category table I have a field for Index number and a field for Categories. The images table has a field for all the images and a field to
specify what category the images are assigned to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of the
number of images assigned to each category from my main table of images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.

[Mark]

Sorted it with this:

SELECT tblCategories.ID, tblCategories.CatName,
COUNT(tblImages.CategoryID)
FROM tblCategories INNER JOIN tblImages
ON tblImages.CategoryID = tblCategories.ID
GROUP BY tblCategories.CatName, tblCategories.ID

Thanks.....
"Mark" <[e-mail]> wrote in message
news:Rm******************@stones.force9.net...

Hi, I have a Access database with two tables; a category table and an images table. In the Category table I have a field for Index number and a field for Categories. The images table has a field for all the images and a field to
specify what category the images are assigned to.

I am trying to write a simple Select query that will return a list of
Categories from my Category table, and in the second colum a count of the
number of images assigned to each category from my main table of images.

Can anybody suggest how I could do this?

Thanks for any help you can give me,

Mark.