SUM with Multiple Tables MySQL

Question

The problem is the one who made the tables is not me. This problem base on database adaptation. So guys I need your help.

I have Tables that needs to be SUM the Quantity of the Items.

I use this code to get the Quantaties from items to orderset_details table.

SELECT
    I.ItemId, I.Code, I.Item, I.Minimum, I.Maximum,
    IFNULL
    (
        (
            SELECT SUM(ROUND(h.Quantity))
            FROM orderset_details AS H
            WHERE H.ItemId = I.ItemId
        )
        , 0
    ) as 'Balance'
FROM items AS I
GROUP BY I.ItemId

OUTPUT

But there are tables that needs to be a NEGATIVE Quantity. My code only gets all Quantity for 1 item at time.

SELECT 
   sts.TransferDate as 'Transaction Date'
  , sts.TransNumber as 'Document Number'
  , orderset_details.Quantity as 'Quantity'
  , '' as 'Po Number'
  , '' as 'Sales Invoice'
  , orderset_details.Cost as 'Cost'
  , orderset_details.SerialNumber as 'Serial Number'
  , orderset_details.LotNumber as 'Lot Number'
  , location.Location as 'Location' 
FROM sts 
LEFT JOIN orderset_details ON sts.OrdersetId = orderset_details.OrdersetId
LEFT JOIN location ON location.LocationId = sts.LocationIdTo  
WHERE orderset_details.ItemId = '4786' 
UNION 
SELECT 
      drs.OrderDate 
    , drs.TransNumber 
    , orderset_details.Quantity 
    , drs.PONumber 
    , '' 
    , orderset_details.Cost 
    , orderset_details.SerialNumber 
    , orderset_details.LotNumber  
    , suppliers.Supplier  
FROM drs 
LEFT JOIN orderset_details ON drs.OrdersetId = orderset_details.OrdersetId 
LEFT JOIN suppliers ON suppliers.SupplierId = drs.SupplierId 
WHERE orderset_details.ItemId = '4786' 
UNION  
SELECT 
      stockadjustment.TransactionDate 
    , stockadjustment.TransactionId 
    , IF(reason.AddsToStock = '1', orderset_details.Quantity, 0 - orderset_details.Quantity)
    , '' 
    , '' 
    , orderset_details.Cost 
    , orderset_details.SerialNumber 
    , orderset_details.LotNumber
    , location.Location   
FROM stockadjustment
LEFT JOIN reason ON reason.ReasonId = stockadjustment.ReasonId
LEFT JOIN orderset_details ON stockadjustment.OrdersetId = orderset_details.OrdersetId 
LEFT JOIN location ON location.LocationId = stockadjustment.LocationId 
WHERE orderset_details.ItemId = '4786' 
UNION  
SELECT 
      issueslip.OrderDate  
    , issueslip.TransNumber 
    , IF(issueslip.OrdersetId = orderset_details.OrdersetId, 0 - orderset_details.Quantity, -1 * orderset_details.Quantity) 
    , '' 
    , '' 
    , orderset_details.Cost 
    , orderset_details.SerialNumber 
    , orderset_details.LotNumber 
    , customers.Customer     
FROM issueslip 
LEFT JOIN orderset_details ON issueslip.OrdersetId = orderset_details.OrdersetId 
LEFT JOIN customers ON customers.CustomerId = issueslip.CustomerId 
WHERE orderset_details.ItemId = '4786' 
UNION  
SELECT 
      invoice.OrderDate 
    , invoice.TransNumber 
    , IF(invoice.OrdersetId = orderset_details.OrdersetId, 0 - orderset_details.Quantity, -1 * orderset_details.Quantity)
    , '' 
    , '' 
    , orderset_details.Cost 
    , orderset_details.SerialNumber 
    , orderset_details.LotNumber 
    , customers.Customer   
FROM invoice 
LEFT JOIN orderset_details ON invoice.OrdersetId = orderset_details.OrdersetId 
LEFT JOIN customers ON customers.CustomerId = invoice.CustomerId 
WHERE orderset_details.ItemId = '4786'

I only need from this Code is for the Negative Quantity Condition LIKE IF(reason.AddsToStock = '1', orderset_details.Quantity, 0 - orderset_details.Quantity) and 2 other more query.

HOW CAN I COMBINE THIS QUERY TO MY FIRST QUERY ABOVE?

trincot · Accepted Answer · 2016-07-24 09:48:39Z

1

You could do something like this:

SELECT    I.ItemId, I.Code, I.Item, I.Minimum, I.Maximum,
          COALESCE(SUM(ROUND(
              CASE SA.ReasonId WHEN R.ReasonId 
                  THEN  h.Quantity 
                  ELSE -h.Quantity 
              END)), 0) as 'Balance'
FROM       items AS I
CROSS JOIN reason AS R
LEFT JOIN  orderset_details AS H
       ON  H.ItemId = I.ItemId
LEFT JOIN  stockadjustment AS SA
       ON  SA.OrdersetId = H.OrdersetId 
WHERE      R.AddsToStock = '1'
GROUP BY  I.ItemId

edited Jul 24, 2016 at 9:48

answered Jul 24, 2016 at 9:16

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Meyka Jograt Over a year ago

Okay ill try it :)

Meyka Jograt Over a year ago

The Query takes more than 300sec

trincot Over a year ago

Check that your database has primary keys for each of the involved tables and indexes on the foreign keys that are used in the query. Is that the case? Also, can you confirm that OrdersetId is a unique column in stockadjustment?

Meyka Jograt Over a year ago

Sorry newb to MySQL i got an error COALESCE(SUM(ROUND( CASE R.AddsToStock WHEN '1' THEN h.Quantity ELSE -h.Quantity CASE Q.OrdersetId WHEN H.OrdersetId THEN h.Quantity ELSE -h.Quantity CASE T.OrdersetId WHEN H.OrdersetId THEN h.Quantity ELSE -h.Quantity END)), 0) as 'Balance'

trincot Over a year ago

OK, I will make a fiddle to make sure. In the meanwhile I made a change to the query (see reason table join and case ... when), which might help to improve performance, but the right indexes must be present like always with SQL to get some decent performance.

|

Collectives™ on Stack Overflow

SUM with Multiple Tables MySQL

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related