Msg 130: Double Aggregates - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Msg 130: Double Aggregates

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-09-25 : 14:01:54

I have code along the following lines that is meant to see how long it's been since I met a person:


SELECT MAX(DATEDIFF(dd, StartTime, GETDATE()) 
AS DaysSinceFirstStarted
FROM FictionalDatabase
GROUP BY Person

However, since the feed is not live, I wanted to replace GETDATE with a value for the latest entry in my database - a trick I use all the time:

 SELECT MAX(DATEDIFF(dd, StartTime, (SELECT MAX(StartTime) from dbo.CallLog))

However, unlike normal, this ends up being an aggregate of an aggregate, and I get Msg 130.
How can I fix this?
Thanks,
Arithmomanaic

---------
Ignorance is bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 14:17:35

I suppose you could put the value in a variable, then use that inside the DATEDIFF. Or you could use a derived table to avoid doing the double aggregates at the same time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 14:20:51

SELECT MAX(DATEDIFF(DAY, c.StartTime, t.mx))
FROM dbo.CallLog AS c
CROSS JOIN (SELECT MAX(StartTime) AS mx FROM dbo.CallLog) AS t
GROUP BY c.Person

E 12°55'05.25"
N 56°04'39.16"

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-09-25 : 14:39:02

quote:
Originally posted by tkizer

I suppose you could put the value in a variable, then use that inside the DATEDIFF.

Sorry. I forgot to mention that it is a view. Can you declare a variable inside an alter view statement? If so, I goofed with this:


ALTER VIEW X As
DECLARE @MaxTime int;
SELECT  @MaxTime = (MAX(StartTime) from dbo.CallLog);
SELECT (regular statement)

Peso, thanks again. Yours worked.
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 15:03:12

Are you using SQL Server 2005?

SELECT DISTINCT	PersonID,
		DATEDIFF(DAY, MIN(StartTime) OVER (PARTITION BY PersonID), MAX(StartTime) OVER (PARTITION BY NULL)) AS Diff
FROM		dbo.CallLog
ORDER BY	PersonID

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources