[SQL Server 2005] Problem SUM in subquery

[cms9651]

Hi all, your help please.

Why this query in SQL Server 2005 I have this output (column PERC is 0 values):

MAT	A	B	C	D	PERC
1D	54050	1195	11468	72659	0
1F	87761	1486	14899	109090	0
1G	54992	1031	10390	73672	0
1H	77815	374	10553	93879	0
1I	71087	1921	13430	91839	0
1M	74800	3338	9659	99771	0
1O	92413	4476	11321	124779	0
1P	76739	1027	11222	100764	0
1Q	47642	2379	7135	66116	0
1R	139038	3888	19909	185092	0
1S	30214	363	4380	39269	0
Tot	806551	21478	124366	1056930	0

instead of this correct output:

MAT	A	B	C	D	PERC
1D	54050	1195	11468	72659	0,918165678
1F	87761	1486	14899	109090	0,954679622
1G	54992	1031	10390	73672	0,901468672
1H	77815	374	10553	93879	0,945280627
1I	71087	1921	13430	91839	0,941190562
1M	74800	3338	9659	99771	0,879985166
1O	92413	4476	11321	124779	0,867213233
1P	76739	1027	11222	100764	0,883132865
1Q	47642	2379	7135	66116	0,86448061
1R	139038	3888	19909	185092	0,879751691
1S	30214	363	4380	39269	0,890193282
Tot	806551	21478	124366	1056930	0,901095626

Can you help me?
Thanks in advance, your help is very appreciated.

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[A],
	[B],
	[C],
	[D],
	(
		[A] + [B] + [C]
	) / ([D]) AS [PERC]
FROM
	(
		SELECT
			LEFT (TZZ, 2) AS MAT,
			COUNT (*) AS [D],
			SUM (
				CASE
				WHEN (
					[RM] = '1'
					OR [RM] = '2'
					OR [RM] = '3'
					OR [RM] = '4'
				)
				AND [DV] IS NOT NULL THEN
					1
				ELSE
					0
				END
			) AS [A],
			SUM (
				CASE
				WHEN (
					[RM] = '5'
					AND [DV] IS NOT NULL
				) THEN
					1
				ELSE
					0
				END
			) AS [B],
			SUM (
				CASE
				WHEN (
					(
						[RM] = '6'
						OR [RM] = '7'
					)
					AND [DV] IS NOT NULL
				) THEN
					1
				ELSE
					0
				END
			) AS [C]
		FROM
			dbo_42
		WHERE
			1 = 1
		AND [TZZ] NOT LIKE 'LG%'
		GROUP BY
			LEFT (TZZ, 2) WITH ROLLUP
	) AS SubQs;

[cpradio]

Your columns A-D are all INT, so you need to CAST at least one of them to DECIMAL in your equation for PERC, like so,

(
		[A] + [B] + [C]
	) / CAST([D] AS DECIMAL(10,8)) AS [PERC]

[cms9651]

Your columns A-D are all INT, so you need to CAST at least one of them to DECIMAL in your equation for PERC, like so,

(
		[A] + [B] + [C]
	) / CAST([D] AS DECIMAL(10,8)) AS [PERC]

thanks for help, but I have this new error in the query:

[Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric.

If tried this:

CAST([D] AS DECIMAL(10,2)) AS [PERC]

I have this output: .91816567803

[cpradio]

I ran the following:

CREATE TABLE dbo_42
(
	[TZZ] VARCHAR(3),
	[RM] VARCHAR(1),
	[DV] VARCHAR(1) NULL
)

INSERT INTO dbo_42 VALUES ('1D', '3', '3')
INSERT INTO dbo_42 VALUES ('1D', '4', NULL)
INSERT INTO dbo_42 VALUES ('1D', '6', '3')
INSERT INTO dbo_42 VALUES ('1D', '7', '3')
INSERT INTO dbo_42 VALUES ('1D', '7', '3')
INSERT INTO dbo_42 VALUES ('1D', '6', '3')
INSERT INTO dbo_42 VALUES ('1D', '4', '3')
INSERT INTO dbo_42 VALUES ('1D', '5', '3')
INSERT INTO dbo_42 VALUES ('1D', '1', '3')

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[A],
	[B],
	[C],
	[D],
	([A] + [B] + [C]) / CAST([D] AS DECIMAL(10,2)) AS [PERC]
FROM
	(
		SELECT
			LEFT (TZZ, 2) AS MAT,
			COUNT (*) AS [D],
			SUM (
				CASE
				WHEN (
					[RM] = '1'
					OR [RM] = '2'
					OR [RM] = '3'
					OR [RM] = '4'
				)
				AND [DV] IS NOT NULL THEN
					1
				ELSE
					0
				END
			) AS [A],
			SUM (
				CASE
				WHEN (
					[RM] = '5'
					AND [DV] IS NOT NULL
				) THEN
					1
				ELSE
					0
				END
			) AS [B],
			SUM (
				CASE
				WHEN (
					(
						[RM] = '6'
						OR [RM] = '7'
					)
					AND [DV] IS NOT NULL
				) THEN
					1
				ELSE
					0
				END
			) AS [C]
		FROM
			dbo_42
		WHERE
			1 = 1
		AND [TZZ] NOT LIKE 'LG%'
		GROUP BY
			LEFT (TZZ, 2) WITH ROLLUP
	) AS SubQs;
	
DROP TABLE dbo_42

And I get the following output:

MAT  A           B           C           D           PERC
---- ----------- ----------- ----------- ----------- ----------------------
1D   3           1           4           9           0.88888888888
Tot  3           1           4           9           0.88888888888

I'm assuming you are in a culture that uses commas for decimal points instead of a period. That is likely a setting on the table/database itself, via localization.

[cpradio]

Bugger, I re-read your error, so the arithmetic overflow would be solved by tweaking the decimal parameters. I won't be able to necessarily tweak those for you, but I can say 10,2 is a typical value used.

[r937]

I have this output: .91816567803

is that not the right answer?

[cms9651]

is that not the right answer?

Why .91816567803 and not 0.91816567803 ?
thank you

[cpradio]

Why .91816567803 and not 0.91816567803 ?
thank you

Okay, for the longest time I wasn't getting the same result as you, I always got the leading zero, so I thought... what could he be doing that I am doing differently? Then it hit me, you might be using Query Analyzer to run your queries.

So I took my SQL code and ran it in Query Analyzer, and ta da! No leading 0's. When I run it in SSMS, it has the leading 0's.

I did some basic searching only found that Query Analyzer removes the leading 0's for all numeric types, but SSMS will only remove them for Floats and Ints. No explanation as to why, or how to change the behavior.

[cms9651]

Okay, many thanks for explanation!