Group By / Order By - 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)

Group By / Order By

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-08-09 : 22:23:46

I've got a query that counts "users" which are grouped by organization. In this case the organization is a simple 2 level organization.

select C.CenterName, B.BranchName, Count(*) as Total from Quiz Q
inner join users U
on Q.UserID=U.UserID
inner join Centers C
on U.center=C.CenterID
left outer join Branches B
on U.branch=B.BranchID
Group by C.CenterName, B.BranchName

This query is straightfoward enough. This is a question about 'ORDER BY'.

I can add

ORDER BY C.CenterName, B.BranchName

to the query (at the end), and it does as expected. Centers and Branches are ordered.

What is trickey (to me anyway) is when I want ROLLUP to subtotal each "Center". Very trickey to do this and "ORDER BY" the result.

Now some of you probably know the problem already, but let me spell out the query with the ROLLUP:

select
case GROUPING(C.CenterName) when 0 then C.CenterName else 'All Centers' end as Center,
case GROUPING (B.BranchName) when 0 then B.BranchName else 'All Branches' end as Branch,
Count(*) as Total from Quiz Q
inner join users U
on Q.UserID=U.UserID
inner join Centers C
on U.center=C.CenterID
left outer join Branches B
on U.branch=B.BranchID
Group by C.CenterName, B.BranchName
WITH ROLLUP

Notice I haven't included 'order by' in the above. Add it at the end after WITH ROLLUP, and it will ruin the layout of the results!

How can I order the ROLLUP query so Centers and Branches are in order?

SamC

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-11 : 12:24:44

Can you provide the DDL and some sample data (DML preferably)?

Jonathan Boott, MCDBA
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources