trying to get average in query but need to groupby

# Thread: trying to get average in query but need to groupby

1. Senior Member
Join Date
Dec 1969
Posts
702

## trying to get average in query but need to groupby

I am wondering if I can do this in one query: I have been trying to get a total average from numbers that have been grouped by name: to make it the idea simplified (not my real query) I want to do this if I can:<BR><BR>john 7<BR>bill 9<BR>john 2<BR><BR>I want the average to show 9 and not 6 because I first group by name to get<BR><BR>john 9<BR>bill 9<BR><BR>and the average is 9<BR><BR>I have been trying to do this in one query but I get the "cannot have aggregate function in expression...."<BR><BR>Can anyone suggest a way to get this done in one query?<BR>

2. Senior Member
Join Date
Dec 1969
Posts
273

## RE: trying to get average in query but need to gro

If I understand correctly you want to sum by name and then take the average.<BR><BR>SELECT AVG(nSum)<BR>FROM (<BR> SELECT First_Name, SUM(nCount) AS nSum<BR> FROM Table<BR> GROUP BY First_Name<BR>) AS q

3. Senior Member
Join Date
Dec 1969
Posts
702

## Yes thats it !!!!

Thank-you, I fit that formula into my full query and it worked !<BR><BR>Thanks for saving 4 hours off my day.

#### Posting Permissions

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