Hello there, I hope your help.
This query working but I have problem with presentation output.
Now the output is:
MAT DVD SALES
MAC L21 24
MAS L21 276
MAC L20 165
Tot L20 411
Tot L21 300
MAN L20 80
MAO L20 81
MAS L20 85
Instead I need this different output:
MAT DVD SALES
MAC L21 24
MAS L21 276
Tot L21 300
MAC L20 165
MAN L20 80
MAO L20 81
MAS L20 85
Tot L20 411
Can you help me?
Thank you in advance
SELECT
COALESCE ([MAT], 'Tot') AS [MAT],
[DVD],
[SALES]
FROM
(
SELECT DISTINCT
[DVD],
CASE
WHEN LEFT (TZZ, 2) = '1D'
OR LEFT (TZZ, 2) = '1F' THEN
'MAO'
WHEN LEFT (TZZ, 2) = '1G'
OR LEFT (TZZ, 2) = '1H' THEN
'MAN'
WHEN LEFT (TZZ, 2) = '1I'
OR LEFT (TZZ, 2) = '1M'
OR LEFT (TZZ, 2) = '1S'
OR LEFT (TZZ, 2) = '1O' THEN
'MAC'
WHEN LEFT (TZZ, 2) = '1P'
OR LEFT (TZZ, 2) = '1Q'
OR LEFT (TZZ, 2) = '1R' THEN
'MAS'
ELSE
'Tot'
END AS [MAT],
COUNT (*) AS [SALES]
FROM
dbo_40
WHERE
1 = 1
AND [TZZ] NOT LIKE 'LG%'
GROUP BY
[DVD],
CASE
WHEN LEFT (TZZ, 2) = '1D'
OR LEFT (TZZ, 2) = '1F' THEN
'MAO'
WHEN LEFT (TZZ, 2) = '1G'
OR LEFT (TZZ, 2) = '1H' THEN
'MAN'
WHEN LEFT (TZZ, 2) = '1I'
OR LEFT (TZZ, 2) = '1M'
OR LEFT (TZZ, 2) = '1S'
OR LEFT (TZZ, 2) = '1O' THEN
'MAC'
WHEN LEFT (TZZ, 2) = '1P'
OR LEFT (TZZ, 2) = '1Q'
OR LEFT (TZZ, 2) = '1R' THEN
'MAS'
ELSE
'Tot'
END WITH ROLLUP
) AS SubQs
WHERE
1 = 1
AND (
[DVD] IN ('L20', 'L21')
);