[SQL Server 2008] Problemi con clausola GROUP BY

[cms9651]

Ciao a tutti.

Non riesco a far funzionare come vorrei questa query.

questo è l'output attuale:

Q MAT NUMBER
1F MOL 103623
1F MOL 103623
1D LIP 69119
1D LIP 69119

Invece avrei bisogno di questo:

Q       MAT     NUMBER
1F      MOL     103623
1D      LIP     69119
        Tot     172742

Potreste aiutarmi?
Grazie

SELECT
        strDTZZ AS Q,
        COALESCE ([MAT], 'Tot') AS [MAT],
        NUMBER
FROM
        (
                SELECT
                        LEFT (DTZZ, 2) AS strDTZZ,
                        CASE
                WHEN LEFT (TZZ, 2) = '1D' THEN
                        'LIP'
                WHEN LEFT (TZZ, 2) = '1F' THEN
                        'MOL'
                WHEN LEFT (TZZ, 2) = '1G' THEN
                        'IRT'
                WHEN LEFT (TZZ, 2) = '1H' THEN
                        'MRE'
                WHEN LEFT (TZZ, 2) = '1I' THEN
                        'UOT'
                WHEN LEFT (TZZ, 2) = '1M' THEN
                        'MAL'
                WHEN LEFT (TZZ, 2) = '1S' THEN
                        'RAS'
                WHEN LEFT (TZZ, 2) = '1O' THEN
                        'PMC'
                WHEN LEFT (TZZ, 2) = '1P' THEN
                        'BUP'
                WHEN LEFT (TZZ, 2) = '1Q' THEN
                        'LAC'
                ELSE
                        'CIS'
                END AS MAT,
                COUNT (*) AS NUMBER,
                FROM
                        dbo_40
                GROUP BY 
                ROLLUP ( LEFT (DTZZ, 2),                        
                        CASE
                WHEN LEFT (TZZ, 2) = '1D' THEN
                        'LIP'
                WHEN LEFT (TZZ, 2) = '1F' THEN
                        'MOL'
                WHEN LEFT (TZZ, 2) = '1G' THEN
                        'IRT'
                WHEN LEFT (TZZ, 2) = '1H' THEN
                        'MRE'
                WHEN LEFT (TZZ, 2) = '1I' THEN
                        'UOT'
                WHEN LEFT (TZZ, 2) = '1M' THEN
                        'MAL'
                WHEN LEFT (TZZ, 2) = '1S' THEN
                        'RAS'
                WHEN LEFT (TZZ, 2) = '1O' THEN
                        'PMC'
                WHEN LEFT (TZZ, 2) = '1P' THEN
                        'BUP'
                WHEN LEFT (TZZ, 2) = '1Q' THEN
                        'LAC'
                ELSE
                        'CIS'
                END
                )
        ) AS SubQ
WHERE
        1 = 1 
AND (strDTZZ = '1D' OR strDTZZ = '1F');