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;