SQL SERVER – Rollup Clause

**cms9651** · Aug 10, 2012 12:05

Hi there, I need your help.
Here is my problem.

I tried this query in dbms SQL Server 2008 and not have error.
I use in this query the syntax GROUP WITH ROLLUP (...) http://blog.sqlauthority.com/2010/02...rollup-clause/ and I have this output:

Code:

MAT	myNumber
MAO	172742
MAE	157907
MAC	341314
MAS	341510
        1013473

I need instead this other output:

Code:

MAT	myNumber
MAO	172742
MAE	157907
MAC	341314
MAS	341510
Tot	1013473

Can you help me?
Thanks in advance.

Code:

SELECT
        [MAT],
	[myNumber]
FROM
        (
                SELECT
		COUNT (*) AS [myNumber],
                        CASE
                WHEN LEFT (TZZ, 2) = 'DD'
                OR LEFT (TZZ, 2) = 'DF' THEN
                        'MAO'
                WHEN LEFT (TZZ, 2) = 'DG'
                OR LEFT (TZZ, 2) = 'DH' THEN
                        'MAE'
                WHEN LEFT (TZZ, 2) = 'DI'
                OR LEFT (TZZ, 2) = 'DM'
                OR LEFT (TZZ, 2) = 'DS'
                OR LEFT (TZZ, 2) = 'DO' THEN
                        'MAC'
                WHEN LEFT (TZZ, 2) = 'DP'
                OR LEFT (TZZ, 2) = 'DQ'
                OR LEFT (TZZ, 2) = 'DR' THEN
                        'MAS'
		ELSE
			'Tot'
                END AS [MAT]
                FROM
                        dbo_40
                GROUP BY
		ROLLUP (
                        CASE
                WHEN LEFT (TZZ, 2) = 'DD'
                OR LEFT (TZZ, 2) = 'DF' THEN
                        'MAO'
                WHEN LEFT (TZZ, 2) = 'DG'
                OR LEFT (TZZ, 2) = 'DH' THEN
                        'MAE'
                WHEN LEFT (TZZ, 2) = 'DI'
                OR LEFT (TZZ, 2) = 'DM'
                OR LEFT (TZZ, 2) = 'DS'
                OR LEFT (TZZ, 2) = 'DO' THEN
                        'MAC'
                WHEN LEFT (TZZ, 2) = 'DP'
                OR LEFT (TZZ, 2) = 'DQ'
                OR LEFT (TZZ, 2) = 'DR' THEN
                        'MAS'
		ELSE
			'Tot'
                END
		)
        ) AS SubQ
WHERE
        1 = 1
ORDER BY
    CASE MAT
        WHEN 'MAO' THEN 1
        WHEN 'MAE' THEN 2
        WHEN 'MAC' THEN 3
        ELSE 4
    END;