Average and count at end of existing table

[jpunja]

Although I know this is not how database-tables should be used, I would like to add two columns (i.e. average and count) to my existing table. The original table:

Name	Age	City
John	10	NY
John	80	London
John	30	NY
Bill	10	NY
Bill	40	NY
Bill	25	NY

<tbody>
</tbody>

I would like to add two columns:

• average of age group by name and CITY = NY
• Count: group by name and CITY = NY

So you get:

Name	Age	City	count	average
John	10	NY	2	20
John	80	London	-	-
John	30	NY	2	20
Bill	10	NY	3	25
Bill	40	NY	3	25
Bill	25	NY	3	25

<tbody>
</tbody>

I can make a table with average and count, and join this table in a second query. But I can't get it into one query (what i realy need). Hopefully you have the solution!

 

[Joe4]

Cross-posted here: Add [count] and [average] to existing table

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[Joe4]

You can do it all in a single query using a subquery, i.e.

SELECT TableName.Name, TableName.Age, TableName.City, SubQuery.CountOfAge, SubQuery.AvgOfAge
FROM TableName 
INNER JOIN 
[COLOR=#0000ff](SELECT TableName.Name, TableName.City, Count(TableName.Age) AS CountOfAge, Avg(TableName.Age) AS AvgOfAge
FROM TableName
GROUP BY TableName.Name, TableName.City) as SubQuery[/COLOR]
ON (TableName.City = SubQuery.City) AND (TableName.Name = SubQuery.Name);

 

[jpunja]

Sorry AND thanks Joe4 !