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

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

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');

quote:

---

Originally posted by visakh16

isnt it enough to add a DISTINCT?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Q 	MAT	NUMBER
1F	MOL	103623
1D	LIP	69119

quote:

---

Originally posted by visakh16

isnt total returned by ROLLUP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Select IsNULL(Q, '') As Q, MAX(Case When GroupingId = 1 Then 'Total' Else MAT End) As MAT, SUM(NUMBER) As NUMBER From
	(
		Select Q, MAX(MAT) As MAT, MAX(NUMBER) As NUMBER, GROUPING(Q) As GroupingId From Ex
		Group By Q
	) As a
Group By IsNULL(Q, '')
With RollUp