SQL Query using COUNT

Discussion in 'ASP General' started by Mark, Jun 29, 2004.

  1. Mark

    Mark Guest

    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, Jun 29, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:RmlEc.850$9.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.
    >
    >
     
    Aaron [SQL Server MVP], Jun 29, 2004
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Jun 29, 2004
    #3
  4. Mark

    Mark Guest

    Thanks Aaron and Bob, its working now!

    "Mark" <> wrote in message
    news:RmlEc.850$9.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, Jun 29, 2004
    #4
  5. Mark

    Mark Guest

    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" <> wrote in message
    news:RmlEc.850$9.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, Jun 30, 2004
    #5
  6. Mark

    Mark Guest

    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" <> wrote in message
    news:RmlEc.850$9.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, Jun 30, 2004
    #6
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Edward
    Replies:
    4
    Views:
    4,625
    William \(Bill\) Vaughn
    Apr 10, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,514
    Anonymous
    Oct 13, 2005
  3. Giovanni Azua

    generic count report for JPQL query

    Giovanni Azua, May 31, 2009, in forum: Java
    Replies:
    0
    Views:
    2,070
    Giovanni Azua
    May 31, 2009
  4. efelnavarro09
    Replies:
    2
    Views:
    953
    efelnavarro09
    Jan 26, 2011
  5. AJ

    Count Query

    AJ, Jul 21, 2006, in forum: ASP General
    Replies:
    1
    Views:
    115
    Bob Barrows [MVP]
    Jul 21, 2006
Loading...

Share This Page