WITH ROLLUP woes - 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

Transact-SQL (2000)

WITH ROLLUP woes

Author

Topic

asdfo
Starting Member

2 Posts

Posted - 2002-05-14 : 16:22:50

I am using a group by WITH ROLLUP in a Pivot query and I notice that if I group by two or more columns I get duplicate rows. Esentially what is happening is I'm getting every row with it's own total! Very odd indeed.

Any suggestions?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-14 : 16:28:03

Each group in the GROUP BY clause will generate it's own roll-up summary row. You can test for these rows using the GROUPING() function, and eliminate them:

SELECT A, B, C, Sum(D) AS Total
FROM myTable
GROUP BY A, B, C
HAVING GROUPING(C)=0

The HAVING clause will reject every summary row for values of C, and only the summary rows for A and B will appear in this query.

There are more examples of the GROUPING function in the CUBE and ROLLUP entries in Books Online.

asdfo
Starting Member

2 Posts

Posted - 2002-05-16 : 12:12:28

Excellent! That worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources