Eugene Elutin (9/6/2012)

---

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