Result of GROUP BY differ on SQL 2000 & 2005

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 2005 Forums

Transact-SQL (2005)

Result of GROUP BY differ on SQL 2000 & 2005

Author

Topic

Alexander Klaus
Starting Member

1 Post

Posted - 2008-11-20 : 22:50:53

Result of this SQL query differ on SQL 2000 from SQL 2005

DECLARE @Test TABLE ( DataDate smalldatetime, Col2 int )

INSERT INTO @Test (DataDate, Col2)	VALUES ('2008-01-01', 1)
INSERT INTO @Test (DataDate, Col2)	VALUES ('2008-02-01', 3)

SELECT  
	DataDate
,	MONTH(DataDate) as DMonth
,	GROUPING(MONTH(DataDate)) as GM
,	GROUPING(DataDate) as GD
FROM	@Test
GROUP BY MONTH(DataDate), DataDate WITH ROLLUP
ORDER BY DataDate

On SQL Server 2000 the result is

DataDate   DMonth  GM   GD 
NULL          1    0    1 
NULL          2    0    1 
NULL        NULL   1    1 
2008-01-01    1    0    0 
2008-02-01    2    0    0

But on SQL Server 2005 the result is

DataDate   DMonth  GM   GD 
NULL        NULL   0    1 
NULL        NULL   0    1 
NULL        NULL   1    1 
2008-01-01    1    0    0 
2008-02-01    2    0    0

Why results are different

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources