SQl - Average Function

[springboardjg]

Hi



I have an INNER JOIN query that returns all the data sets that I need from two different tables. However, I am trying to AVERAGE a columnn retrieved from the second table and GROUP BY a column retrieved from the first table. But am getting the following error...

"You tried to execute a query that does not include the specified expression 'Site_Serial' as part of an aggregate function"

Here is the SQL Query causing this error...

SELECT tbl_SiteInformation.Average_Grouping, tbl_SiteInformation.Site_Serial, tbl_SiteSerialsAndWeeklyAverages.HSI_Year, tbl_SiteSerialsAndWeeklyAverages.HSI_Month, AVG(tbl_SiteSerialsAndWeeklyAverages.Weekly_Average) AS MoMCurrentMonth
FROM tbl_SiteInformation INNER JOIN tbl_SiteSerialsAndWeeklyAverages ON tbl_SiteInformation.Site_Serial = tbl_SiteSerialsAndWeeklyAverages.Site_Serial
WHERE tbl_SiteSerialsAndWeeklyAverages.HSI_Year = 2011
AND tbl_SiteSerialsAndWeeklyAverages.HSI_Month = 4
AND (tbl_SiteSerialsAndWeeklyAverages.Weekly_Average IS NOT NULL)
AND (SELECT tbl_SiteSerialsAndWeeklyAverages.Weekly_Average
FROM tbl_SiteSerialsAndWeeklyAverages AS tbl_SiteSerialsAndWeeklyAveragesAlias
WHERE tbl_SiteSerialsAndWeeklyAverages.Site_Serial = tbl_SiteSerialsAndWeeklyAveragesAlias.Site_Serial AND tbl_SiteSerialsAndWeeklyAveragesAlias.HSI_Year=2011 AND tbl_SiteSerialsAndWeeklyAveragesAlias.HSI_Month=3 AND tbl_SiteSerialsAndWeeklyAveragesAlias.Weekly_Average IS NOT NULL)
GROUP BY Average_Grouping;

Any help would be greatly appreciated. I can also post the results returned by the query if the AVG function and the GROUP BY are excluded.

Thanks

James

[NoellaG]

Hi,

if you use a group by instruction, each member of the SELECT statement has to be part of the group by statement or have an aggregation function.

greetings
NG

[Rhino373]

So assuming there is only 1 site_serial when grouping by Average_Grouping just group by first for that column. If it could return multiple then you would group by it as well unless it's a number in which case you'll have to either exclude it altogether or use a math function like sum.