CODEWITHSUNDEEP
sqlsql-server
kalabo
kalabo

Reputation: 564

SQL Average set of Group By Results

I am doing the following query to get the count of bookings grouped by week.

SELECT 
  Count([Id]) as Sessions
  FROM [RitualDB].[dbo].[SessionBooking]
  Where MemberId = 1
  Group by DATEPART(WEEK, Date)

This returns me a nice set of results. What i want to do is get the average of all these results. I have tried

SELECT 
  AVG(Count([Id])) as average
  FROM [RitualDB].[dbo].[SessionBooking]
  Where MemberId = 1
  Group by DATEPART(WEEK, Date)

However I get the following error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Can someone give me a pointer in the right direction.

Upvotes: 0

Views: 60

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use a subquery:

SELECT AVG(Sessions)
FROM(
    SELECT 
        Count([Id]) AS SESSIONS
    FROM [RitualDB].[dbo].[SessionBooking]
    WHERE MemberId = 1
    Group BY DATEPART(WEEK, Date)
)t

Upvotes: 4

Related Questions