SQL Query using COUNT

M

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.
 
A

Aaron [SQL Server MVP]

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

Bob Barrows [MVP]

Mark said:
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
 
M

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"
 
M

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.....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top