Average a group

[Guest]

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.

 

[Dirk Goldgar]

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.

Would

SELECT JobID, Avg(Age) As AverageAge
FROM YourTable

give you what you want?

 

[Douglas J Steele]

Would

SELECT JobID, Avg(Age) As AverageAge
FROM YourTable

give you what you want?

No, it wouldn't, Dirk.

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

might though! <g>

 

[Dirk Goldgar]

No, it wouldn't, Dirk.

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

might though! <g>

Argh!

 

[Guest]

Doug,

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


John

 

[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!

 

[Guest]

Thank you very much.

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!