Query Help - 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)

Query Help

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-04-03 : 10:01:49

Hi Guys

I have the following query:

SELECT TOP 1000
AVG(DATEDIFF(wk,MAX(r.DateEntered),GETDATE())) AS 'AverageNumberOfWeeksSinceLastTransaction'
FROM dbo.Customer AS c WITH (NOLOCK)
LEFT JOIN dbo.Receipt AS r WITH (NOLOCK)
ON c.CustomerID = r.CustomerID

I receive the following error:
Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Does anyone know what I can do to solve the problem?

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 10:12:11

Like the error message says, you can not aggregate an expression containing an aggregate. Post some sample data and output you want. Its not clear what you want from your query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 10:12:28

Try

SELECT TOP 1000
AVG(DATEDIFF(wk,r.DateEntered,GETDATE())) AS 'AverageNumberOfWeeksSinceLastTransaction'
FROM dbo.Customer AS c WITH (NOLOCK)
LEFT JOIN 
(
	select CustomerID,MAX(DateEntered) as DateEntered from dbo.Receipt 
	group by CustomerID
) AS r WITH (NOLOCK)
ON c.CustomerID = r.CustomerID

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 10:18:19

quote:

Originally posted by madhivanan

Try

SELECT TOP 1000
AVG(DATEDIFF(wk,r.DateEntered,GETDATE())) AS 'AverageNumberOfWeeksSinceLastTransaction'
FROM dbo.Customer AS c WITH (NOLOCK)
LEFT JOIN 
(
	select CustomerID,MAX(DateEntered) as DateEntered from dbo.Receipt 
	group by CustomerID
) AS r WITH (NOLOCK)
ON c.CustomerID = r.CustomerID

Madhivanan

Failing to plan is Planning to fail

Thats what I was thinking he wants. But looking at "top 1000" there, may be he wants something else. You query would return one row. avg against all customers.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 10:18:27

or this (if you are just looking for a single avg for all customer's latest transaction)


select avg(wksSince) AS [AverageNumberOfWeeksSinceLastTransaction]
from   (
       select c.customerid
              ,datdiff(wk, max(r.DateEntered), getdate()) as wksSince
       FROM dbo.Customer AS c WITH (NOLOCK)
       LEFT JOIN dbo.Receipt AS r WITH (NOLOCK)
       ON c.CustomerID = r.CustomerID
       group by c.customerid
       ) d

Be One with the Optimizer
TG

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-04-03 : 10:18:57

Hey Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources