Adbrite

Monday, September 10, 2007

NULL values and WITH ROLLUP

When you using WITH ROLLUP, you can get group total and full total within the result set but imagine when you allow null columns and writing T-SQL statement with using WITH ROLLUP for groups by your GROUP BY columns? It calculate correct values but we can’t sort it out.



USE mydb

GO

DROP TABLE ITEM

GO

CREATE TABLE ITEM

( categoryCode varchar(10) NOT NULL,

itemCode varchar(10)NOT NULL,

qty int NOT NULL

)

GO

INSERT INTO ITEM VALUES('category 1','item 1',5)

INSERT INTO ITEM VALUES('category 1','item 2',4)

INSERT INTO ITEM VALUES('category 1','item 3',2)

INSERT INTO ITEM VALUES('category 2','item 1',12)

INSERT INTO ITEM VALUES('category 2','item 2',8)

INSERT INTO ITEM VALUES('category 3','item 2',1)

GO

SELECT isnull(categoryCode,' Full') AS categoryCode,

isnull(itemCode,' Total is ') AS itemCode,

sum(qty ) totQty

FROM ITEM

GROUP BY categoryCode,itemCode

WITH ROLLUP

GO

--After setting allow nulls

ALTER TABLE ITEM ALTER COLUMN categoryCode varchar(10) NULL

ALTER TABLE ITEM ALTER COLUMN itemCode varchar(10) NULL

GO

INSERT INTO ITEM VALUES('category 1',null,5)

INSERT INTO ITEM VALUES('category 2',null,4)

INSERT INTO ITEM VALUES(null,'item 3',2)

GO

SELECT isnull(categoryCode,' Full') AS categoryCode,

isnull(itemCode,' Total is ') AS itemCode,

sum(qty ) totQty

FROM ITEM

GROUP BY categoryCode,itemCode

WITH ROLLUP

GO

No comments: