Ordering a ROLLUP - 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 2000 Forums

SQL Server Development (2000)

Ordering a ROLLUP

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-05 : 22:42:42

My favorite topic (again) grouping, adding, totaling.

SELECT
CASE WHEN GROUPING(Centername) = 1
THEN 'Grand Total'
ELSE Centername
END AS Centername,
count(*) as Total,
sum(case when CR.CRecordFinish IS NULL THEN 0 ELSE 1 END) AS [Completions] ,
' ' + cast(cast(sum(case when CR.CRecordFinish IS NULL THEN 0 ELSE 1 END) * 100.0 /
case when count(*)=0 then 1 else count(*) end as numeric (5,0)) as varchar) + '% ' as [Percent Completed] ,

sum(CASE WHEN CRAcknowledge IS NULL THEN 0 WHEN CRAcknowledge < 1 then 1 ELSE 0 END) as Denied

FROM Users U

INNER JOIN Centers C ON C.CenterID=U.CenterID

LEFT OUTER JOIN courseRecords CR ON CR.UserID=U.UserID AND CR.CourseID = @CourseID

WHERE U.Inactive = 0

GROUP BY Centername with Rollup

Results
-------------
Centername,Total,Completions,Percent Completed,Denied
HDQ,299,218, 73% ,1
NESDIS,1947,1718, 88% ,2
NMFS,3791,3275, 86% ,3
NOS,1900,1668, 88% ,2
NWS,5976,5245, 88% ,5
OAR,1912,1596, 83% ,8
OFA,1199,929, 77% ,0
OMAO,626,441, 70% ,0
Grand Total,17650,15090, 85% ,21

Is there a way to ORDER BY so that the Centername column is alphabetical, yet keep the 'Grand Total' at the bottom?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-05 : 22:47:27

Yep:

ORDER BY GROUPING(CenterName), CenterName

Grouping() will be zero for non-summary rows, and 1 for summary rows.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-05 : 22:51:05

Sam -- just wondering: do you use reporting writing software or do you do most of your reporting through the query analyzer ?

Anyway, try

SELECT * FROM
(
Your SQL
) A
ORDER BY CASE WHEN Centername = 'Grand Total' THEN 1 ELSE 0 END ASC, CenterName ASC

or something along those lines.

Rob's is much better -- I always forget about GROUPING(). Cool.

- Jeff

Edited by - jsmith8858 on 03/05/2003 22:52:03

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-05 : 22:52:39

Is this guy good or what ?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-05 : 22:56:51

Hi Jeff,

I'm a mimimalist on the ASP side. It's kind of nice to have the stored procedure do what used to take loops to do in ASP.

Do you recommend any ASP reporting libraries?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-05 : 23:03:08

You know, if you have a fair amount of data to retrieve, and a lot of funky formatting to do to it, AND you don't want to or can't use something like Crystal Reports, you can A) output the SQL to XML and use XSLT to format it, or B) use GetRows to dump the data into an array and use it from there. The performance will be lightning fast with the latter.

Also, you can use ADO to output XML even if you aren't using SQL Server 2000. It saves it in a really funky format though...BUT, you can also save a shaped ADO recordset with it, which can be really handy for nested reports.

I'm sure I've seen some reporting modules for ASP, but they probably have a lot of overhead that you don't really need. And I have a feeling they'll just be loops of rs.MoveNext and response.write calls, might as well encode the page in Morse for all the performance you'll get out of it.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-05 : 23:16:36

Thanks Rob,

If I change my technique, it'll be to move to .NET datagrids. Jeff was making noises like he had a favorite library. I haven't heard any accolades about ASP reporting libraries.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-05 : 23:33:27

Hehehehehehe, there was a 3 part article on ASPToday about a month ago where they did an extensive, and I mean everything-AND-the-kitchen-sink-and-the-guy-who-installed-it extensive, examination of performance tuning a datagrid. It turns out that the generally accepted methods of ADO rs.MoveNext and Response.Write was MILES faster than a standard datagrid with viewstate turned on. They ended up writing their OWN rendering routine in order to get it to MATCH the standard ADO. GetString and GetRows STILL outperform a datagrid in its best running form.

Sadly ASPToday is a paid subscription site, and I don't think it was one of their free articles, but it was one of the better ones they've done in a quite a while. Some guy also wrote one yesterday about a lightweight replacement for an ADO Recordset that uses GetRows and adds his own custom recordset methods/properties to it, it was REALLY cool looking.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-06 : 14:59:11

I suppose if I would search, I would find. So I'm being lazy here.

I'd like to see a good coding example of getrows building a table in asp.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-06 : 15:24:09

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10176
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18303
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6501

There's a GetRows and some GetStrings in there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources