Average a group

J

John

Hi,

I have two fields in my query.
example:

JobID Age
9 20
11 24
21 45
11 45
21 25
9 60

I would like to know how I can find the average age for each jobID using a
query?Thanks.
 
D

Dirk Goldgar

John said:
Hi,

I have two fields in my query.
example:

JobID Age
9 20
11 24
21 45
11 45
21 25
9 60

I would like to know how I can find the average age for each jobID
using a query?Thanks.
Click to expand...

Would

SELECT JobID, Avg(Age) As AverageAge
FROM YourTable

give you what you want?
 
D

Douglas J Steele

Dirk Goldgar said:
Would

SELECT JobID, Avg(Age) As AverageAge
FROM YourTable

give you what you want?
Click to expand...

No, it wouldn't, Dirk.

SELECT JobID, Avg(Age) As AverageAge
FROM YourTable
GROUP BY JobID

might though! <g>
 
J

John

Doug,

Can you explain how I do this in my query please? I'm new this concept!
Thanks.


John
 
D

Douglas J. Steele

Easiest way is to create a brand new query and add the table to it.

Drag the JobID and Age fields into the grid.

On the View menu, select "Totals" (or you can click on the icon on the tool
bar that has a capital sigma on it: that's the character that looks like a
backwards 3, or an M on its side) That will add a new row to your query grid
labelled Total that will have "Group By" filled in under both fields.

Change the "Group By" to "Avg" under the Age field, and you're done.

If you go into the SQL view of the query (through the View menu), you should
see essentially the same SQL that Dirk & I were suggesting. Since that's a
lot quicker just to post that SQL than the details above, that's why we tend
to do it that way!
 
J

John

Thank you very much.



Douglas J. Steele said:
Easiest way is to create a brand new query and add the table to it.

Drag the JobID and Age fields into the grid.

On the View menu, select "Totals" (or you can click on the icon on the tool
bar that has a capital sigma on it: that's the character that looks like a
backwards 3, or an M on its side) That will add a new row to your query grid
labelled Total that will have "Group By" filled in under both fields.

Change the "Group By" to "Avg" under the Age field, and you're done.

If you go into the SQL view of the query (through the View menu), you should
see essentially the same SQL that Dirk & I were suggesting. Since that's a
lot quicker just to post that SQL than the details above, that's why we tend
to do it that way!
Click to expand...
 
Top