Sumar Totales segun consumo por maquina.

[Former Member]

Estimados:

Tengo la siguiente query en la cual me muestra el consumo real de una maquina por orden de fabricacion y su costo total segun rango de fechas.

Lo que necesito es que me entregue el total consumido por maquina, es decir:

Maquina 050 Total 100

En estos momentos lo que muestra es :

Maquina 050 Total 20

Maquina 050 Total 30

Maquina 050 Total 25

Maquina 050 Total 10

Maquina 050 Total 15

El codigo de la maquina esta reflejado en T3 (ItemCode)

Adjunto Query:

SELECT T0.[DocEntry], T0.[BaseRef], T0.[ItemCode], T5.[ItmsGrpCod], T0.[Quantity], T1.[DocDate], T2.[DocNum],T3.[ItemCode], T4.[ItmsGrpCod], T3.[BaseQty], T4.[AvgPrice], T2.[ItemCode], T0.[Quantity]T3.[BaseQty] as 'Total Seg. Maq', (T0.[Quantity]T3.[BaseQty])* T4.[AvgPrice] as 'Total Costo Maq.' FROM IGN1 T0 INNER JOIN OIGN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OWOR T2 ON T0.[BaseRef] = T2.[DocNum] INNER JOIN WOR1 T3 ON T2.DocEntry = T3.DocEntry INNER JOIN OITM T5 ON T0.ItemCode = T5.ItemCode INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode WHERE T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1] AND T4.[ItmsGrpCod] = 108 AND T5.[ItmsGrpCod] <> 103 order by T2.[DocNum]

Gracias de antemano.

VMC.

[former_member188440 · Accepted solution]

Mete un distinct itemcode y un SUM donde traes el consumo real y el costo

[Former Member · Accepted solution]

Mauricio:

He intentado colocar las instrucciones que me indicas pero no me resulta. Me podrias indicar mejor como debo hacerlo?

Saludos,

VMC.

[Former Member · Accepted solution]

Mauricio:

Logre sumar los totales por maquina, pero igual me muestra el detalle y me gustaria que solo me mostrara el total.

Aqui esta la nueva query:

SELECT T3.[ItemCode], T0.[DocEntry], T0.[BaseRef], T0.[ItemCode], T5.[ItmsGrpCod], T0.[Quantity], T1.[DocDate], T2.[DocNum], T4.[ItmsGrpCod], T3.[BaseQty], T4.[AvgPrice], T2.[ItemCode], T0.[Quantity]T3.[BaseQty] as 'Total Seg. Maq', (T0.[Quantity]T3.[BaseQty])* T4.[AvgPrice] as 'Total Costo Maq.' FROM IGN1 T0

INNER JOIN OIGN T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OWOR T2 ON T0.[BaseRef] = T2.[DocNum]

INNER JOIN WOR1 T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN OITM T5 ON T0.ItemCode = T5.ItemCode

INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode

WHERE T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1] AND T4.[ItmsGrpCod] = 108 AND T5.[ItmsGrpCod] <> 103

UNION ALL

SELECT distinct T3.[ItemCode], null, null, null, null, sum (T0.[Quantity]), null, null, null, sum (T3.[BaseQty]), null, null, sum (T0.[Quantity]T3.[BaseQty])as 'Total Seg. Maq', sum((T0.[Quantity]T3.[BaseQty])* T4.[AvgPrice] )as 'Total Costo Maq.' FROM IGN1 T0

INNER JOIN OIGN T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OWOR T2 ON T0.[BaseRef] = T2.[DocNum]

INNER JOIN WOR1 T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN OITM T5 ON T0.ItemCode = T5.ItemCode

INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode

WHERE T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1] AND T4.[ItmsGrpCod] = 108 AND T5.[ItmsGrpCod] <> 103

group by T3.[ItemCode]

Gracias.

VMC.

[felipe_loyolarodriguez · Accepted solution]

Viviana

Cuando muestras totales, no debes colocar campos que son unicos por documento, por ejemplo el docentry, docnum, docdate, ya que esos campos no se pueden agrupar. Si quieres agrupar por ItemCode, solo coloca el Item y las sumas de los campos de los documentos, si quieres un total por documento, agrega el documento a la query pero si no lo vas a ocupar es mejor que no los coloques.

Prueba este query

SELECT DISTINCT T3.ItemCode, SUM(T0.Quantity), SUM(T0.Quantity*T3.BaseQty)'Total Seg. Maq', ((T0.QuantityT3.BaseQty) *T4.AvgPrice)'Total Costo Maq.' 
FROM IGN1 T0
INNER JOIN OIGN T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OWOR T2 ON T0.BaseRef = T2.DocNum
INNER JOIN WOR1 T3 ON T2.DocEntry = T3.DocEntry
INNER JOIN OITM T5 ON T0.ItemCode = T5.ItemCode
INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode
WHERE T1.DocDate >='[%0]' AND T1.DocDate <='[%1]' AND T4.ItmsGrpCod = 108 AND T5.ItmsGrpCod != 103
GROUP BY T3.ItemCode

Slds

[Former Member · Accepted solution]

Felipe:

La consulta que me enviaste manda el siguiente error:

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Column 'IGN1.Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]C

Saludos,

VMC.

[felipe_loyolarodriguez · Accepted solution]

Vivi

Faltaba un SUM

SELECT DISTINCT T3.ItemCode, SUM(T0.Quantity), SUM(T0.Quantity*T3.BaseQty)'Total Seg. Maq', SUM((T0.QuantityT3.BaseQty) *T4.AvgPrice)'Total Costo Maq.' 
FROM IGN1 T0
INNER JOIN OIGN T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OWOR T2 ON T0.BaseRef = T2.DocNum
INNER JOIN WOR1 T3 ON T2.DocEntry = T3.DocEntry
INNER JOIN OITM T5 ON T0.ItemCode = T5.ItemCode
INNER JOIN OITM T4 ON T3.ItemCode = T4.ItemCode
WHERE T1.DocDate >='[%0]' AND T1.DocDate <='[%1]' AND T4.ItmsGrpCod = 108 AND T5.ItmsGrpCod != 103
GROUP BY T3.ItemCode

Slds

[Former Member · Accepted solution]

Felipe:

Tienes toda la razon...jijiji....solo me habia dado cuenta de que faltaba un *, pero no me fije en la palabra SUM.

Funcionó pefecto.

Gracias,

VMC.