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 databaseselect distinct servername,firstoccurrence,lastoccurrence,eventid,key,group,node,summarysum(tally) as NumberofEvents,--sum(count(*)) over(partition by dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotalsum(count(*)) over(partition by servername, dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotalbyserverfrom databasetablewhere firstoccurrence >= '2013-02-04' and firstoccurrence < '2013-02-08'and lastoccurrence >= '2013-02-04' and lastoccurrence < '2013-02-08'group byfirstoccurrence,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 @FooVALUES(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 @FooGROUP 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 likeuse databaseselect *,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 daytotalbyserverfrom(select distinct servername,firstoccurrence,lastoccurrence,eventid,key,group,node,summarysum(tally) as NumberofEvents,count(*) as cntfrom databasetablewhere firstoccurrence >= '2013-02-04' and firstoccurrence < '2013-02-08'and lastoccurrence >= '2013-02-04' and lastoccurrence < '2013-02-08'group byfirstoccurrence,lastoccurrence,servername,eventid,alertkey,alertgroup,node,summary)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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?MadhivananFailing 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 4incorrect 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 4incorrect 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 onwardsMadhivananFailing 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,summarysum(tally) as NumberofEvents,count(*) as cntinto #tempfrom databasetablewhere firstoccurrence >= '2013-02-04' and firstoccurrence < '2013-02-08'and lastoccurrence >= '2013-02-04' and lastoccurrence < '2013-02-08'group byfirstoccurrence,lastoccurrence,servername,eventid,alertkey,alertgroup,node,summaryselect t1.*,t2.daytotal,t3.daytotalbyserverfrom #temp t1join (select datediff(dd,0,lastoccurrence) as daynumber,sum(cnt) as daytotal from #temp group by datediff(dd,0,lastoccurrence) )t2on 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) )t3on t3.servername = t1.servernameand t3.daynumber = datediff(dd,0,t1.lastoccurrence) drop table #temp[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|