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