mardi 10 avril 2018

Can't figure out how to deal with Sum of values in mysql

Good morning,

I am trying to create a mysql output from my incoming and outgoing products. This works, but I can't get the values for all products in the same row. Hope someone can help me with this. (In the column orderinhoud-deellevering.aantal there are many times NULL values.)

this is my code:

SELECT
  orderinhoud.Artnr,
  orderinhoud.Description,
  If(voorraadmutatie.waarde = 1, Sum(voorraadmutatie.waarde * (orderinhoud.Amount -  Coalesce((`orderinhoud-deellevering`.aantal), 0))), 0) AS INKOMEND,
  If(voorraadmutatie.waarde <> 1, Sum(voorraadmutatie.waarde * (orderinhoud.Amount -  Coalesce((`orderinhoud-deellevering`.aantal), 0))), 0) AS UITGAAND
FROM
  orderinhoud
  RIGHT OUTER JOIN `order` ON `order`.id = orderinhoud.orderid
  LEFT OUTER JOIN voorraadmutatie ON `order`.voorraadmutatie = voorraadmutatie.Id
  LEFT OUTER JOIN `orderinhoud-deellevering` ON orderinhoud.orderinhoudid = `orderinhoud-deellevering`.orderinhoudid
WHERE
`order`.leverancierid = 22
GROUP BY
  orderinhoud.Artnr,
  orderinhoud.Description,
  `order`.deliverydatetotal,
  `order`.leverancierid,
  `order`.commissiecompleet,
  `order`.voorraadmutatie    

--- The output that I get is the following ---

Artnr   Description INKOMEND    UITGAAND
K30000  ROUND  ALARM    2400    0
K30000  ROUND  ALARM    0      -788
K30001  SQUARE ALARM    1900    0
K30001  SQUARE ALARM    0      -1020

But the output that I want to have is this:

Artnr   Description INKOMEND    UITGAAND
K30000  ROUND  ALARM    2400    -788
K30001  SQUARE ALARM    1900    -1020

I hope someone can help me with this.

Thanks already!

Aucun commentaire:

Enregistrer un commentaire