[SQL Server 2005] Query UNION syntax
Hi all, I return after time because I have problem with this sql query in my DB Sql Server 2005.
I have this output when execute this query UNION:
MAT TR TS
MANO .4704 .1729
MANE .6707 .2918
MACO .5940 .1991
MASO .4872 .2596
Tot .5615 .2299
SELECT
[MAT],
[%TR],
[%TS]
FROM
(
SELECT
[MAT],
[%TR],
[%TS]
FROM
TestTable
UNION
SELECT
COALESCE ([MAT], 'Tot') AS [MAT],
SUM ([%TR]),
SUM ([%TS])
FROM
TestTable
GROUP BY
(MAT)
) q
ORDER BY
CASE MAT
WHEN 'MANO' THEN
1
WHEN 'MANE' THEN
2
WHEN 'MACO' THEN
3
WHEN 'MASO' THEN
4
ELSE
5
END;
In my sql server table for MAT equal to MANO I have two records: LOML and PILL.
If execute this other query UNION I have this output:
MAT TR TS
LOML .3701 .1356
PILL .5938 .2188
Tot .9639 .3544
SELECT
[MAT],
[%TR],
[%TS]
FROM
(
SELECT
[MAT],
[%TR],
[%TS]
FROM
TestTable
UNION
SELECT
COALESCE ([MAT], 'Tot') AS [MAT],
SUM ([%TR]),
SUM ([%TS])
FROM
TestTable
GROUP BY
(MAT) WITH ROLLUP
) q
ORDER BY
CASE ([MAT])
WHEN 'LOML' THEN
1
WHEN 'PILL' THEN
2
WHEN 'ERML' THEN
3
WHEN 'TRIL' THEN
4
WHEN 'CMPL' THEN
5
WHEN 'LAML' THEN
6
WHEN 'SARL' THEN
7
WHEN 'TOUL' THEN
8
WHEN 'CALL' THEN
9
WHEN 'PUBL' THEN
10
ELSE
11
END;
The values of row ‘Tot’ columns ‘TR’ and ‘TS’ it’s wrong because in my first query I have for MAT ‘MANO’ this values for columns ‘TR’ and ‘TS’:
MAT TR TS
MANO .4704 .1729
I was hope to have the same output in my second query:
MAT TR TS
LOML .3701 .1356
PILL .5938 .2188
Tot .4704 .1729
Instead I have:
MAT TR TS
LOML .3701 .1356
PILL .5938 .2188
Tot .9639 .3544
Why?
Can you help me?
Thank you.