Help With AGGREGATE Query!

[Lezza]

I need to use a SELECT statement to find the Genus (animal-type) that costs the most to feed per 30 days. I've got the following query that gives me the average cost per Genus, but I need only return the ONE a_Genus_ID that costs the most to feed. I basically need GS.a_Genus_ID WHERE MAX(AVG(Units_oz* 30)). Can anyone please help me figure this out?

SELECT
GS.a_Genus_ID,
AVG(Units_oz * 30) AS AvgCost_Per_Month
FROM a_GenusSpecies GS
JOIN GenusSpeciesTOFood GSTF
ON GSTF.a_GenusSpecies_ID = GS.a_GenusSpecies_ID
JOIN a_Food F
ON F.a_Food_ID = GSTF.a_Food_ID
GROUP BY a_Genus_ID

Thanks in advance,
Lezza

 

[JamesLean]

Try this:

SELECT TOP 1
  GS.a_Genus_ID, 
  AVG(Units_oz * 30) AS AvgCost_Per_Month
FROM a_GenusSpecies GS
  JOIN GenusSpeciesTOFood GSTF
    ON GSTF.a_GenusSpecies_ID = GS.a_GenusSpecies_ID
  JOIN a_Food F
    ON F.a_Food_ID = GSTF.a_Food_ID
GROUP BY a_Genus_ID
ORDER BY 2 DESC

--James

 

[ClaireHCM]

select top 1 * from
(SELECT
GS.a_Genus_ID,
AVG(Units_oz * 30) AS AvgCost_Per_Month
FROM a_GenusSpecies GS
JOIN GenusSpeciesTOFood GSTF
ON GSTF.a_GenusSpecies_ID = GS.a_GenusSpecies_ID
JOIN a_Food F
ON F.a_Food_ID = GSTF.a_Food_ID
GROUP BY a_Genus_ID) B order by AvgCost_Per_Month desc