SQL query that applies aggregate function over another aggregate function

Question

I have several query results that use one or more aggregate functions and a date GROUP-BY so they look something like this:

Date     VisitCount(COUNT)  TotalBilling(SUM)
1/1/10     234                15765.21
1/2/10     321                23146.27
1/3/10     289                19436.51

The simplified SQL for the above is:

SELECT 
  VisitDate, 
  COUNT(*) AS VisitCount, 
  SUM(BilledAmount) AS TotalBilling 

FROM Visits 

GROUP BY VisitDate

What I would like is a way to apply an aggregate function such as AVG to one of the columns in the result set. For example, I would like to add "AvgVisits" and "AvgBilling" columns to the result set like this:

Date     VisitCount(COUNT)  TotalBilling(SUM)  AvgVisits  AvgBilling
1/1/10     234                15765.21          281.3      19449.33
1/2/10     321                23146.27          281.3      19449.33
1/3/10     289                19436.51          281.3      19449.33

SQL does not permit the application of an aggregate function to another aggregate function or a subquery, so the only ways I can think to do this are by using a temporary table or by iterating through the result set and manually calculating the values. Are there any ways I can do this in MSSQL2008 without a temp table or manual calculation?

Answer 1

with cteGrouped as (
SELECT 
  VisitDate, 
  COUNT(*) AS VisitCount, 
  SUM(BilledAmount) AS TotalBilling
FROM Visits 
GROUP BY VisitDate),
cteTotal as ( 
SELECT COUNT(*)/COUNT(DISTINCT VisitDate) as AvgVisits,
     SUM(BilledAmount)/COUNT(DISTINCT VisitDate) as AvgBilling
FROM Visits)
SELECT *
FROM cteGrouped
CROSS JOIN cteTotal;

You can achieve the same with sub-queries, I just find CTEs more expressive.

Answer 2

Well if you are specifically trying to avoid using temporary tables i believe it could be done using a Common Table Expression.

Answer 3

Something Similar to

select *,avg(visitcount) over(),
avg(totalbilling) over()
from(
SELECT 
  VisitDate, 
  COUNT(*) AS VisitCount, 
  SUM(BilledAmount) AS TotalBilling 

FROM Visits 
GROUP BY VisitDate) as a