Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    9

    Unanswered: GROUP BY multiple fields?

    Is there a way in Access SQL to GROUP BY multiple fields? For example, if you have this data:

    person|code|number|items|age
    0 | PE | 0112 | 8 | 25
    1 | PE | 0112 | 6 | 7
    2 | PE | 0112 | 2 | 12
    0 | TE | 2105 | 3 | 28
    1 | TE | 2105 | 2 | 22
    0 | GE | 2105 | 1 | 35
    1 | GE | 2105 | 2 | 20
    0 | FE | 5263 | 5 | 45
    1 | FE | 5263 | 3 | 43
    2 | FE | 5263 | 5 | 15
    3 | FE | 5263 | 1 | 18

    So basically, wherever person is 0, I need the result rolled up into that one (0 means a family, so 1, 2, 3, 4, etc... are other family members). So I need to GROUP BY DISTINCT number AND code (there are 4 2105's but 2 different codes) COUNT those, and then SUM items. So the result would be:

    person|code|number|TotalPersons|TotalItems | Age
    0 | PE | 0112 | 3 | 16 | 25
    0 | TE | 2105 | 2 | 5 | 28
    0 | GE | 2105 | 2 | 3 | 35
    0 | FE | 5263 | 4 | 14 | 45

    This is what I have so far, however, it's only a first step.

    SELECT person, code, number
    FROM table
    WHERE person = 0;

    That gave me total families.

    If I do GROUP BY instead of WHERE, then it's not accurate since it would group all 2105's together.

    I tried doing more stuff but it gives me aggregate error since I tried to roll up all the COUNTS and SUMS into the 0 rows.

    Anyone have any idea how I could do this? Any help is greatly appreciated!
    Last edited by agalloch; 06-23-10 at 10:33.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    yes it is very simple; make a normal select query, look for the aggregate symbol (greek E episilon)....leave the first 3 column as 'group' default and change the 4th column to 'sum'

  3. #3
    Join Date
    Jun 2010
    Posts
    9
    Ok thanks so that worked for me. This is the query.

    SELECT person, code, COUNT(number) AS TotalPersons, SUM(items) AS totalItems
    FROM table
    GROUP BY code, number;

    So that got the accurate number. Now here's the second issue. All the fields have an age field in the original table, but I only want to bring up the age for the 0 people since they're the ones shown in the result. Is that possible? Or do I have to do it separately, and then JOIN the two tables later on? When I just include the Age field, it gives me the Aggregate data error.

    Edited my original post just so you can see what I mean by Age in the tables.
    Last edited by agalloch; 06-23-10 at 16:39.

  4. #4
    Join Date
    Jun 2010
    Posts
    9
    Nevermind, I got it figured out. To move over the age, I just did FIRST(Age) as MemberAge, and that brought over the Age of all of 0's as well.

Posting Permissions

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