SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server 2005] Query UNION syntax

    [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:
    Code:
    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:
    Code:
    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':
    Code:
    MAT	TR	TS
    MANO	.4704	.1729
    I was hope to have the same output in my second query:
    Code:
    MAT	TR	TS
    LOML	.3701	.1356
    PILL	.5938	.2188
    Tot	.4704	.1729
    Instead I have:
    Code:
    MAT	TR	TS
    LOML	.3701	.1356
    PILL	.5938	.2188
    Tot	.9639	.3544
    Why?
    Can you help me?
    Thank you.

  2. #2
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I have found the solution.

    My error in second query is this:
    Code:
    SUM ([%TR]),
    SUM ([%TS])
    I need use this AVG for the correct output:
    Code:
    AVG ([%TR]),
    AVG ([%TS])
    http://msdn.microsoft.com/en-us/library/ms177677.aspx
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

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