[SQL Server 2005] Query UNION syntax

[cms9651]

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

[cms9651]

I have found the solution.

My error in second query is this:

SUM ([%TR]),
SUM ([%TS])

I need use this AVG for the correct output:

AVG ([%TR]),
AVG ([%TS])

http://msdn.microsoft.com/en-us/library/ms177677.aspx