We ran across the need to create some statistics on a set of results created by a query (with a group by statement). This kind of scenario is real simple by using a temporary table:
-- Create your temporary table
WITH tempTableName (theID, numberOfInstances) AS
(
SELECT theID, COUNT(*) AS numberOfInstances
FROM tableName
GROUP BY theID
)
-- Generate your statistics
SELECT AVG(numberOfInstances) AS [Average Number of Instances]
FROM tempTableName
WHERE numberOfInstances > 0 ;
GO