Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: Query Help

    It's been a while since I've had to do this and for the life of my I can't remember how to do it.

    I have a query that has these fields: Group and Location

    Group Location
    570 5700
    570 5701
    571 5710
    572 5720
    573 5730
    573 5750
    573 5733
    573 5753

    What I want to do is summarize the data in a query. I want to count the number of Locations per Group. For instance 573 = 4, 570 = 2, 571 = 1. I can do it with 2 queries but I would like to do it all in one query.

    Any help is appreciated.

    C

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    was slightly uneasy about the field named 'group' so i tried it - it doesn't seem to upset things:

    SELECT group, count(location) AS myCount
    FROM tblMine GROUP BY group

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Thanks for the reply but I guess that I didn't make myself clear enough in my explanation. The data is from a query to start with. The qryMyQuery will return the data that I listed with Group and Location. I was looking for a way to modify qryMyQuery to summarize the data. I was hoping to do it all in one query instead of two.

    I hope that makes sense.

    C

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Doesn't make a right lot of sense, because you're returning a different result set... therefore two queries would be the way to do this...

    Any particular reason why you only want to use one?
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Sorry about the confusion but I think I have it figured out. Here's what I came up with:

    Select GROUP, Count(Location)
    From (Select GROUP, Location From MyTable) as subquery
    Group By GROUP

    The subquery isn't as simple as what I have above but I'm at home and the really query is at work. And yeah...maybe the field name Group wasn't the best choice

    Thanks again for the help.

    C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •