Suma Algebraica de 2 Select SQL Server 2012

[ParKraS]

Buenos Tardes, tengo los siguientes Select en SQL Server 2012, y necesito hacer una suma algebraica de ambos Select del los campos cantidad y total, ya que el resultado de cada uno me da valores positivos y negativos.


SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '[%1]' OR '[ %1]'=' ') and (T0.[DocDate] <= '[%2]' OR '[%2]'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

UNION ALL

SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '[%1]' OR '[ %1]'=' ') and (T0.[DocDate] <= '[%2]' OR '[ %2]'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]
ORDER BY T5.[U_CTS_Tienda],T5.SLPNAME,T1.[U_bgn_grupoarticulo]

El resultado de estos Select es el siguiente:

T-010122    V20026844   T-01    5   PEDRO   ROPA    3   30.000
T-010122    V20026844   T-01    5   PEDRO   ROPA    -1  -10.000

Necesito que se vea así:

T-010122    V20026844   T-01    5   PEDRO   ROPA    2   20.000

Agradecería mucho su ayuda ya que no tengo mucha experiencia con SQL.

Intente hacer esto y no me funciono:

SELECT *
FROM

(

SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

UNION ALL
SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

)

Me da error de Sintaxis incorrecta cerca de ')' en la linea 12 que vendría siendo el ultimo paréntesis.

[rubentexas]

podrias crear una vista con los dos select unidos y finalmente agrupar el resultado para que de los 20.

SELECT *
FROM

(

SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

UNION ALL
SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

) AS EL_PARENTESIS

te falta el alias de la tabla que estas simulando con los parentesis.

Esto te dara de nuevo 2 lineas, PERO finalmente pones de nuevo el GROUP y sale OK.

lo mas simple cuando hago esto es ver el select como sale y luego poner los parentesis, el alias y agrupar por los nombres de los campos que salen de ese select.
GROUP BY U_CTS_Tienda,SLPNAME,U_CTS_Cedula,U_CTS_Vendedor,U_CTS_Cargo,U_bgn_grupoarticulo
Recorda que el select afuera del parentesis ahora tendran el alias "EL_PARENTESIS" que le puse a proposito para mostrar el bug.

[ParKraS]

Según entiendo quedaría así:

SELECT *
FROM

(

SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

UNION ALL
SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

) AS SUMA

GROUP BY SUMA.[U_CTS_Tienda], SUMA.SLPNAME,SUMA.[U_CTS_Cedula],SUMA.U_CTS_Vendedor,SUMA.[U_CTS_Cargo],SUMA.[U_bgn_grupoarticulo],SUMA.CANTIDAD,SUMA.TOTAL


Hasta aquí todo bien pero en que punto realizo la suma de los campos CANTIDAD Y TOTAL, El Group by me agrupa pero no me hace la operación matemática, me sigue mostrando las dos lineas.

[rubentexas]

te falto el SUM


SELECT  /* NO "select * "si usas asi no el group no funcionara como queres.*/
SUMA.[U_CTS_Tienda], SUMA.SLPNAME,SUMA.[U_CTS_Cedula],SUMA.U_CTS_Vendedor,SUMA.[U_CTS_Cargo],SUMA.[U_bgn_grupoarticulo],SUM(SUMA.CANTIDAD), SUM(SUMA.TOTAL) /*fijate que puse SUM(campo) esta es la funcion que realmente sumara.*/
FROM

(

SELECT T0.U_CTS_Vendedor, T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo],T5.SLPNAME, T1.[U_bgn_grupoarticulo], SUM(T1.[Quantity]) AS CANTIDAD, SUM(T1.[LineTotal]) AS TOTAL FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

UNION ALL
SELECT T0.U_CTS_Vendedor,T5.[U_CTS_Cedula],T5.[U_CTS_Tienda],T5.[U_CTS_Cargo], T5.SLPNAME, T1.[U_bgn_grupoarticulo],SUM((T1.[Quantity]*(-1))) AS CANTIDAD, SUM((T1.[LineTotal]*(-1))) AS TOTAL FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode LEFT JOIN OSLP T5 ON T5.U_CTS_VENDEDORPOS = T0.U_CTS_Vendedor
WHERE ( T0.[DocDate] >= '2017-09-01' OR '2017-09-01'=' ') and (T0.[DocDate] <= '2017-09-01' OR '2017-09-01'=' ')
GROUP BY T5.[U_CTS_Tienda], T5.SLPNAME,T5.[U_CTS_Cedula],T0.U_CTS_Vendedor,T5.[U_CTS_Cargo],T1.[U_bgn_grupoarticulo]

) AS SUMA

GROUP BY SUMA.[U_CTS_Tienda], SUMA.SLPNAME,SUMA.[U_CTS_Cedula],SUMA.U_CTS_Vendedor,SUMA.[U_CTS_Cargo],SUMA.[U_bgn_grupoarticulo] /* de aca sacas los campos que tienen funcion y pones la funcion en el select*/

[ParKraS]

Graciassssss! Ahora si me anda de 10.