|
Home > Archive > IIS ASP > June 2004 > SQL Query using COUNT
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Query using COUNT
|
|
|
| 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] 2004-06-29, 5:55 pm |
| 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" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@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] 2004-06-29, 5:55 pm |
| 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"
| |
|
| Thanks Aaron and Bob, its working now!
"Mark" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@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.
>
>
| |
|
| 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" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@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.
>
>
| |
|
| 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" <m@notmyemailaddress.com> wrote in message
news:RmlEc.850$Fc7.199326@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.
>
>
|
|
|
|
|