SQL SERVER – Rollup Clause
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;
