# Thread: GROUP BY multiple fields?

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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
•