Aggregate function error - help please!

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 2008 Forums

Transact-SQL (2008)

Aggregate function error - help please!

Author

Topic

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-11 : 17:34:26

Hi,

I have a query that I need to run on both a SQL 2008 DB and also a SQL 2000 DB.

When I run the query on the 2008 DB, no errors are returned, however in SQL 2000 I get an error. The tables in each database are similar but not exactly the same, however they both have the fields that are being selected in the query and I can't understand why one DB is giving this error whilst for the other DB the query works perfectly.

The query is below:


use database

select distinct 
servername,
firstoccurrence,
lastoccurrence,
eventid,
key,
group,
node,
summary
sum(tally) as NumberofEvents,
--sum(count(*)) over(partition by dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotal
sum(count(*)) over(partition by servername, dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotalbyserver

from databasetable
where firstoccurrence >= '2013-02-04' and firstoccurrence < '2013-02-08'
and lastoccurrence >= '2013-02-04' and lastoccurrence < '2013-02-08'

group by
firstoccurrence,
lastoccurrence,
servername,
eventid,
alertkey,
alertgroup,
node,
summary

and the error that I get is below:

Server: Msg 130, Level 15, state 1, Line 14. Cannot 
perform an aggregate function on an expression containing an aggregate or sub query

What is the issue? its really bugging me. Any help greatly appreciated!

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-11 : 17:43:27

The Error tells you: you cannot aggregate and aggregate. For example:

DECLARE @Foo TABLE (ID INT, Val INT)
INSERT @Foo
VALUES
(1, 11),
(2, 12),
(3, 13),
(4, 14),
(5, 15),
(1, 16),
(2, 17),
(3, 18),
(4, 19),
(5, 20)

SELECT ID, SUM(COUNT(*))
FROM @Foo
GROUP BY ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 22:59:23

you need to find the count(*) first in subquery and then apply sum over it.

ie like


use database

select *,
sum(cnt) over(partition by dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotal,
sum(cnt) over(partition by servername, dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotalbyserver
from
(
select distinct 
servername,
firstoccurrence,
lastoccurrence,
eventid,
key,
group,
node,
summary
sum(tally) as NumberofEvents,
count(*) as cnt
from databasetable
where firstoccurrence >= '2013-02-04' and firstoccurrence < '2013-02-08'
and lastoccurrence >= '2013-02-04' and lastoccurrence < '2013-02-08'

group by
firstoccurrence,
lastoccurrence,
servername,
eventid,
alertkey,
alertgroup,
node,
summary
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-12 : 05:47:15

Why would it work on one database and not the other?

I don't understand this....

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-12 : 05:57:58

quote:
Originally posted by mitin

Why would it work on one database and not the other?

I don't understand this....

thanks

Which RDBMS did it work in?

Madhivanan

Failing to plan is Planning to fail

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-12 : 06:59:48

My original query didn't work in the 2000 DB but did in the 2008 instance.

Also, the code that visakh16 kindly posted works in 2008 but not in 2000. As in 2000 I get the following error:


server: msg 156, level 15, state 1, line 4
incorrect syntax near the keyword 'over'

What is going on here?? dont get it and its annoying!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-12 : 07:46:33

quote:
Originally posted by mitin

My original query didn't work in the 2000 DB but did in the 2008 instance.

Also, the code that visakh16 kindly posted works in 2008 but not in 2000. As in 2000 I get the following error:

server: msg 156, level 15, state 1, line 4
incorrect syntax near the keyword 'over'

What is going on here?? dont get it and its annoying!!

The new OVER clause with SUM will work from 2008 onwards

Madhivanan

Failing to plan is Planning to fail

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-12 : 07:52:28

How could the query be altered so that it worked for 2000? ca anyone help this is pretty urgent...

thanks very much!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 10:14:30

[code]
select
servername,
firstoccurrence,
lastoccurrence,
eventid,
key,
group,
node,
summary
sum(tally) as NumberofEvents,
count(*) as cnt
into #temp
from databasetable
where firstoccurrence >= '2013-02-04' and firstoccurrence < '2013-02-08'
and lastoccurrence >= '2013-02-04' and lastoccurrence < '2013-02-08'

group by
firstoccurrence,
lastoccurrence,
servername,
eventid,
alertkey,
alertgroup,
node,
summary

select t1.*,
t2.daytotal,
t3.daytotalbyserver
from #temp t1
join (select datediff(dd,0,lastoccurrence) as daynumber,sum(cnt) as daytotal
from #temp
group by datediff(dd,0,lastoccurrence)
)t2
on t2.daynumber = datediff(dd,0,t1.lastoccurrence)
join (select servername,datediff(dd,0,lastoccurrence) as daynumber,sum(cnt) as daytotalbyserver
from #temp
group by servername,datediff(dd,0,lastoccurrence)
)t3
on t3.servername = t1.servername
and t3.daynumber = datediff(dd,0,t1.lastoccurrence)

drop table #temp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources