Solved

Group by x,x,x with rollup works, group by rollup (x,x,x) doesn't 2005

Posted on 2011-03-03
3
1,066 Views
Last Modified: 2012-05-11
I'm trying to use the rollup function. The sql server 2005 manual states that 'with rollup' is not ISO standard, and will be removed, don't do any development work with this format, by which I presume they mean use the 'group by rollup' function. They give the example code below in the documentation

SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c);

Open in new window


I have tried this and it returns

Msg 195, Level 15, State 10, Line 3
'ROLLUP' is not a recognized built-in function name.

The database is set to level 90 (SQL 2005)

I am probably going to go with using 'with rollup' for now, but just want to check that the SQL documentation is actually wrong for this function. Or do I need to check for patches to the SQL server engine?
 
0
Comment
Question by:PCIIain
  • 2
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35026779
what is the database compatibility version of your database?
if it's set to 80 (sql 2000), it will indeed pretend not to know that function ...
0
 
LVL 13

Author Comment

by:PCIIain
ID: 35026980
Nope, SQL Server 2005 (90)

it works with 'group by x,x,x with rollup' which is set to be removed from SQL server (I don't think 2000 will work with 'with rollup' either will it?), it's the group by rollup (x,x,x); that fails.

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35106192
actually, I have gone through the documentations, and found that the ROLLUP operator is only as from sql 2008:

sql 2005 doc:
http://msdn.microsoft.com/en-us/library/ms177673%28v=SQL.90%29.aspx
you have the GROUP BY xxx WITH ROLLUP  (or WITH CUBE)

sql 2008 doc:
http://msdn.microsoft.com/en-us/library/ms177673%28v=SQL.100%29.aspx
you have the ROLLUP operator

not sure where I read previously that the rollup operator would be available in SQL 2005 also...
in the Analytics Server eventually...

so, in short: the answer is: not available in sql 2005
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question