Sean Lange (9/6/2012)
...This query will produce the same results:
SELECT
COALESCE ([MAT], 'Tot') AS [MAT],
[DVD],
SUM ([SALES])
FROM
TestTable
GROUP BY
(MAT),
[DVD] --with rollup
ORDER BY
[DVD] DESC
...
Try to add one more data row:
INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'WAS', N'L12', N'3');
To ensure that 'Tot' record is placed as the last per DVD, you should treat it separately eg.
ORDER BY [DVD] DESC, CASE WHEN MAT = 'Tot' THEN 1 ELSE 0 END, MAT