Algebraic Sum of 2 Select SQL Server 2012

Question

Algebraic Sum of 2 Select SQL Server 2012

Navigation

#1 by (2 votes)
#2 by (0 votes)

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

sql sql-server query

asked by Johan Fabi 11.09.2017 в 14:02

source

2 answers

Doubt with hashmap [closed] CSS3: A transform-origin for each transform?

score 2 · Answer 1

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(CANTIDAD) AS CANTIDAD, 
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.

score 0 · Answer 2

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.CANTIDAD) AS CANTIDAD, 
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]