Hello there, I hope your help.
This query working but I have problem with presentation output.
Now the output is:
Code:
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:
Code:
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
Code:
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')
);