# Algebraic Sum of 2 Select SQL Server 2012

2

Good morning, I have the following Select in SQL Server 2012, and I need to do an algebraic sum of both Select of the quantity and total fields, since the result of each one gives me positive and negative values.

``````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]
``````

The result of these Select is as follows:

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

I need you to look like this:

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

I would greatly appreciate your help as I do not have much experience with SQL. : D

asked by Johan Fabi 11.09.2017 в 14:02
source

2

What I'm going to do, I do not know if it's the best solution, since I do not have more data about your tables. Apparently your selects are from different tables, with very strange names, for what I suppose will be some old system.

At your current select, wrap it in another select.

``````SELECT
U_CTS_Vendedor,
U_CTS_Cedula,
U_CTS_Tienda,
U_CTS_Cargo,
SLPNAME,
U_bgn_grupoarticulo,
SUM(TOTAL) AS TOTAL
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] >= '[%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]
)
``````

Notice that you also take out an order by, which in this case is totally redundant.

Neither do I understand the names between braces, none of the fields have spaces in their name.

0

Thanks for the help, I could solve it ... I was missing the alias of the new Select

``````SELECT

SUMA.U_CTS_Vendedor AS CODIGO_VENDEDOR,
SUMA.[U_CTS_Tienda] AS TIENDA,
SUMA.SLPNAME AS VENDEDOR,
SUMA.[U_CTS_Cedula] AS CEDULA,
SUMA.[U_CTS_Cargo] AS CARGO,
SUMA.[U_bgn_grupoarticulo] AS RUBRO,
SUM(SUMA.TOTAL) AS TOTAL

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]
``````