SQL - UNION de dos consultas...

Hola foro, quien podra ayudarme con estas dos cunsultas.. necesito tenerlas unidas las dos consultas... Generan los mismos campos una vez ejecutada cada sentencia. Aqui las tienen La duda es como unirlas UNION ..

Gracias de antemano Foro..

Efren

SELECT [GROUP]
,SUM(CASE WHEN [GRUPO] = 'FIN' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'FIN'
,SUM(CASE WHEN [GRUPO] = 'G ADM' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'G ADM'
,SUM(CASE WHEN [GRUPO] = 'H RES' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'H RES'
,SUM(CASE WHEN [GRUPO] = 'IT' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'IT'
,SUM(CASE WHEN [GRUPO] = 'MATL' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'MATL'
,SUM(CASE WHEN [GRUPO] = 'MOLD & MAINT' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'MOLD & MAINT'
,SUM(CASE WHEN [GRUPO] = 'QA' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'QA'
,SUM(CASE WHEN [GRUPO] = 'SBU-A' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'SBU-A'
,SUM(CASE WHEN [GRUPO] = 'SBU-B' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'SBU-B'
,SUM(CASE WHEN [GRUPO] = 'SRTUP' THEN (ET.[EXPENSE]) ELSE 0 END) AS 'SRTUP'
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP] ET
INNER JOIN [Budget].[dbo].[BPCS_PO] PO
ON SUBSTRING(PO.[ACCOUNT],5,15) = ET.[ACCOUNT]
INNER JOIN [Budget].[dbo].[expenseGroup] G
ON ET.[ACCOUNT] = G.[ACCOUNT]
INNER JOIN [Budget].[dbo].[DeptosGroups] D
ON ET.[DPTO] = D.[DEPTO]
AND ET.[PERIODO] = '8'
GROUP BY [GROUP]
ORDER BY [GROUP]

SELECT [GROUP]
,SUM(CASE WHEN [GRUPO] = 'FIN' THEN ([EXPENSE]) ELSE 0 END) AS 'FIN'
,SUM(CASE WHEN [GRUPO] = 'G ADM' THEN ([EXPENSE]) ELSE 0 END) AS 'G ADM'
,SUM(CASE WHEN [GRUPO] = 'H RES' THEN ([EXPENSE]) ELSE 0 END) AS 'H RES'
,SUM(CASE WHEN [GRUPO] = 'IT' THEN ([EXPENSE]) ELSE 0 END) AS 'IT'
,SUM(CASE WHEN [GRUPO] = 'MATL' THEN ([EXPENSE]) ELSE 0 END) AS 'MATL'
,SUM(CASE WHEN [GRUPO] = 'MOLD & MAINT' THEN ([EXPENSE]) ELSE 0 END) AS 'MOLD & MAINT'
,SUM(CASE WHEN [GRUPO] = 'QA' THEN ([EXPENSE]) ELSE 0 END) AS 'QA'
,SUM(CASE WHEN [GRUPO] = 'SBU-A' THEN ([EXPENSE]) ELSE 0 END) AS 'SBU-A'
,SUM(CASE WHEN [GRUPO] = 'SBU-B' THEN ([EXPENSE]) ELSE 0 END) AS 'SBU-B'
,SUM(CASE WHEN [GRUPO] = 'SRTUP' THEN ([EXPENSE]) ELSE 0 END) AS 'SRTUP'
FROM [Budget].[dbo].[BPCS_PO] PO
INNER JOIN [Budget].[dbo].[expenseGroup] G
ON SUBSTRING(PO.[ACCOUNT],5,15) = G.[ACCOUNT]
INNER JOIN [Budget].[dbo].[DeptosGroups] D
ON SUBSTRING(PO.[ACCOUNT],1,3) = D.[DEPTO]
WHERE SUBSTRING(PO.[ACCOUNT],5,5) LIKE '7%'
AND [PERIODO] = '8'
GROUP BY [GROUP]
ORDER BY [GROUP]

La respuesta es la que tienes.
El comando Union hace que los registros traidos por los dos queries (o más), distinguiendo a los repetidos, para no ponerlos duplicados. Si quieres prevenir esta distinción, tienes que poner Union All.

Espero que te sirva.

Saludos.

Gracias PacoPaz pero eso ya lo intente de varias formas, no se en que estoy fallando que no me da. intenten con UNION, UNION ALL y no logro conseguir que se unan esos dos resultados.

uno de los errores que sale es este..

Msg 1033, Level 15, State 1, Line 22

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Msg 170, Level 15, State 1, Line 42

Line 42: Incorrect syntax near ')'.

Lo hice de esta manera

SELECT (

SELECT .. GROUP BY CAMPO1
UNION ALL
SELECT... GROUP BY CAMPO1
)
GROUP BY CAMPO1

ya intente de varias formas y no he logrado juntarlos, si alguie podria hacerlo y darmelo como ejemplo para probarlo seria mucho mejor..

de antemano gracias.

Efren




)

SELECT CONVERT(CHAR(25),[GROUP])
,CONVERT(CHAR(12), SUM(CASE WHEN [GRUPO] = 'FIN' THEN (ET.[EXPENSE]) ELSE '0' END)) AS 'FIN'
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP] ET
INNER JOIN [Budget].[dbo].[expenseGroup] G
ON ET.[ACCOUNT] = G.[ACCOUNT]
INNER JOIN [Budget].[dbo].[DeptosGroups] D
ON ET.[DPTO] = D.[DEPTO]
AND ET.[PERIODO] = '8'
GROUP BY [GROUP]
ORDER BY [GROUP]
UNION
SELECT CONVERT(CHAR(25),[GROUP])
,CONVERT(CHAR(12), SUM(CASE WHEN [GRUPO] = 'FIN' THEN ([EXPENSE]) ELSE '0' END)) AS 'FIN'
FROM [Budget].[dbo].[BPCS_PO] PO
INNER JOIN [Budget].[dbo].[expenseGroup] G
ON SUBSTRING(PO.[ACCOUNT],5,15) = G.[ACCOUNT]
INNER JOIN [Budget].[dbo].[DeptosGroups] D
ON SUBSTRING(PO.[ACCOUNT],1,3) = D.[DEPTO]
WHERE SUBSTRING(PO.[ACCOUNT],5,5) LIKE '7%'
AND [PERIODO] = '8'
GROUP BY [GROUP]
ORDER BY [GROUP]

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'UNION'.

Quita el primer Order By.
Las consultas de unión sólo requieren uno que ordenará tooooodo el resultado.

Espero que te sirva.

Saludos.

Gracias pacopaz por tu interes en ayudarme, tienes razon se me paso quitar los order by, al hacer quitarlos tampoco me deja.. mira te pongo un ejemplo mas sencillo con un solo dato..

Query 1:
Este query si me corre, pero me repite las cuentas (ACCOUNT) osea me las agrupa en cada query pero al unir las tablas ya no van agrupadas..

SELECT SUBSTRING([ACCOUNT],5,5) AS ACCOUNT
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_PO]
WHERE SUBSTRING([ACCOUNT],5,5) LIKE '7%'
GROUP BY ACCOUNT
UNION ALL
SELECT [ACCOUNT]
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP]
GROUP BY ACCOUNT

Query2 :
En este query me arroja un error:
SELECT ACCOUNT, EXPENSE FROM
(
SELECT SUBSTRING([ACCOUNT],5,5) AS ACCOUNT
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_PO]
WHERE SUBSTRING([ACCOUNT],5,5) LIKE '7%'
GROUP BY ACCOUNT
UNION ALL
SELECT [ACCOUNT]
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP]
GROUP BY ACCOUNT
)
GROUP BY ACCOUNT

Error:
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'GROUP'.

Query 3:
Si le quito los Group by a cada query para que sean agrupados por el del query externo me arroja el sigueinte error:
SELECT ACCOUNT, EXPENSE FROM
(
SELECT SUBSTRING([ACCOUNT],5,5) AS ACCOUNT
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_PO]
WHERE SUBSTRING([ACCOUNT],5,5) LIKE '7%'

UNION ALL
SELECT [ACCOUNT]
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP]

)
GROUP BY ACCOUNT

Error:
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ')'.

Alguna Sugerencia?

Se me paso ponerle el SUM a la columna EXPENSE pero de igual manera no corre..

SELECT ACCOUNT, sum(EXPENSE) FROM
(SELECT SUBSTRING([ACCOUNT],5,5) AS ACCOUNT
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_PO]
WHERE SUBSTRING([ACCOUNT],5,5) LIKE '7%'

UNION ALL
SELECT [ACCOUNT]
,SUM([EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP]
)
GROUP BY ACCOUNT

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'GROUP'.

Y si le pones un alias al resultado de la subconsulta y a las tablas de ella, además de agrupar las sumas de la subconsulta? Algo como esto:

SELECT T.ACCOUNT, sum(T.EXPENSE) FROM
(SELECT SUBSTRING([P].[ACCOUNT],5,5) AS ACCOUNT
,SUM([P].[EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_PO] AS P
WHERE SUBSTRING([P].[ACCOUNT],5,5) LIKE '7%'
GROUP BY SUBSTRING([P].[ACCOUNT],5,5)

UNION ALL

SELECT [E].[ACCOUNT] AS ACCOUNT
,SUM([E].[EXPENSE]) AS EXPENSE
FROM [Budget].[dbo].[BPCS_EXPENSE_TEMP] AS E
GROUP BY [E].[ACCOUNT]
) AS T
GROUP BY T.ACCOUNT

A ver si esto ya no te arroja errores.

Saludos.

Pecopaz.. diste con el clavo... tienes razon el group by necesitaba diferenciar entre cada tabla es por eso que el Alias era sumamente necesario.

Buen dia y muchas gracias de nuevo..

Efren