MAT	NUMB-VER-EXE	NUMB-VER-RIC	%TAX	
a	47642		7228	.15	15,17%
b	92413		14323	.15	15,50%
c	77815		10878	.14	13,98%
d	74800		19601	.26	26,20%
e	87761		8297	.09	9,45%
f	54050		9234	.17	17,08%
g	76739		20936	.27	27,28%
h	30214		8695	.29	28,78%
i	139038		13388	.10	9,63%
l	71087		13514	.19	19,01%
m	54992		10440	.19	18,98%
Tot	806551		136534	2.00	16,93%


CREATE TABLE TestTable (
	[MAT] VARCHAR (100),
	[NUMB-VER-EXE] INT,
	[NUMB-VER-RIC] INT,
	[%TAX] DECIMAL (10, 4)
);

INSERT INTO TestTable (
	[MAT],
	[NUMB-VER-EXE],
	[NUMB-VER-RIC] INT,
	[%TAX]
) SELECT
	[MAT],
	[NUMB-VER-EXE],	
	[NUMB-VER-RIC],
	[NUMB-VER-RIC] / (
		CAST (
			[NUMB-VER-EXE] AS DECIMAL (10, 4)
		)
	) AS [%TAX]
FROM
	(
		SELECT MAT,
		SUM (
			CASE
			WHEN (
				[RISC-MOT] = '1'
				OR [RISC-MOT] = '2'
				OR [RISC-MOT] = '3'
				OR [RISC-MOT] = '4'
			)
			AND [DATE-V] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [NUMB-VER-EXE],		
		SUM (
			CASE
			WHEN (
				[COD] IN (
					'A01',
					'A02',
					'A06',
					'A07',
					'A08',					
					'A47'
				)	
			      )
			)
			AND [DATE-V] IS NOT NULL THEN
				1
			ELSE
				0
			END
		) AS [NUMB-VER-RIC]
	FROM
		dbo_40
	GROUP BY
		MAT
	) AS SubQs;


SELECT
		[MAT],
		[NUMB-VER-EXE],
        	[NUMB-VER-RIC],
		[%TAX]
	FROM
		(
			SELECT
				[MAT],
				[NUMB-VER-EXE],
                        	[NUMB-VER-RIC],
				[%TAX]
			FROM
				TestTable
			UNION
				SELECT
					COALESCE ([MAT], 'Tot') AS [MAT],
					SUM (
						[NUMB-VER-EXE]
					),	
					SUM (
						[NUMB-VER-RIC]
					),				
					SUM ([%TAX])
				FROM
					TestTable
				GROUP BY
					(mat) WITH ROLLUP
		) q
	ORDER BY
		CASE (MAT)
	WHEN 'Tot' THEN
		1
	END;

DROP TABLE TestTable;

quote:

---

Originally posted by webfred

try DECIMAL(12,4) instead of DECIMAL(10,4)

---

Too old to Rock'n'Roll too young to die.

---