Aggregate of an Aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Aggregate of an Aggregate

Author

Topic

AdenJones
Starting Member

17 Posts

Posted - 2009-05-12 : 21:42:55

Okay I know you can't have an aggregate of an aggregate in the following form:

SELECT AVG(COUNT(*)) AS Attendance
FROM tblAttendance
WHERE DateAtt >= SomeDate
AND DateAtt <= SomeDate
GROUP BY DateAtt

So how do you get an aggregate of an aggregate?

AdenJones
Starting Member

17 Posts

Posted - 2009-05-12 : 21:52:13

I also tried:

SELECT AVG(Attendance) AS Avg_Attendance
FROM (SELECT COUNT(*) AS Attendance
FROM tblAttendance
WHERE DateAtt >= '2007-1-1'
AND DateAtt <= '2009-5-13'
GROUP BY DateAtt)

AdenJones
Starting Member

17 Posts

Posted - 2009-05-12 : 22:05:42

I needed to give the derived table an alias so I've got it sorted. Thanks anyway. end code is:
SELECT AVG(Attendance) AS Avg_Attendance
FROM (SELECT COUNT(*) AS Attendance
FROM tblAttendance
WHERE DateAtt >= '2007-1-1'
AND DateAtt <= '2009-5-13'
GROUP BY DateAtt) AS T

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-13 : 02:55:06

You mean you aliased the derived table?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources