# Thread: [SQL Server 2005] Problem SUM in subquery

1. ## [SQL Server 2005] Problem SUM in subquery

Why this query in SQL Server 2005 I have this output (column PERC is 0 values):
Code:
```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```
Code:
```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?
Code:
```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;```

2. 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,

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

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,

Code:
```(
[A] + [B] + [C]
) / CAST([D] AS DECIMAL(10,8)) AS [PERC]```
thanks for help, but I have this new error in the query:
Code:
`[Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric.`
If tried this:
Code:
`CAST([D] AS DECIMAL(10,2)) AS [PERC]`
I have this output: .91816567803

4. Originally Posted by cms9651
I have this output: .91816567803
is that not the right answer?

5. Originally Posted by r937
is that not the right answer?
Why .91816567803 and not 0.91816567803 ?
thank you

6. Originally Posted by cms9651
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.

7. I ran the following:
Code:
```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:
Code:
```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.

8. 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.

9. Okay, many thanks for explanation!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•