mysqlignition

MySQL 3 Table Union and running SUM of one Column

Here is an example of a Table Component I am trying to create. enter image description here

Here is the Query I am currently attempting (unsuccessfully). The three tables are wp_ko, rack and bt_runin. The problem I am having is the with the SUM of the column labeled "Result". I would like column "Result" to show the Balance of the additions and subtractions in column "Volume".

SELECT StartTime, name, Lot, fv, koVol/31 AS Volume, SUM(koVol/31) AS Result
FROM wp_ko
WHERE fv = 11 AND name = "SEZI" AND lot LIKE "1007%"
UNION
SELECT StartTime, destName, destLot, dest, TotVol, SUM(TotVol)
FROM rack
WHERE dest = 11 AND destname = "SEZI" AND destlot LIKE "1007%"
UNION
SELECT StartTime, srcName, srcLot, Source, TotVol*-1, SUM(TotVol*-1)
FROM rack
WHERE Source = 11 AND srcName = "SEZI" AND srcLot LIKE "1007%"
UNION
SELECT StartTime, Name, Lot, fv, Total_Bls*-1, Total_Bls*-1)
FROM bt_runin
WHERE fv = 11 AND Name = "SEZI" AND Lot LIKE "1007%"

This is the resulting table I get.

enter image description here

Solution

  • I was able to figure this one out with the help of a friend. Below is the query that works and below that is a pic of the resulting table.

    SELECT q1.StartTime AS "Start Time", 
    q1.ProductCode as "Product Code",
q1.Lot AS "Lot",
q1.FV AS "FV",
q1.Volume AS "Volume",
@totVol := @totVol + q1.Volume AS "Balance",
q1.Source AS "Source"
FROM 
    (SELECT 
        StartTime AS "StartTime", Name AS "ProductCode", Lot AS "Lot", fv AS "FV", 
koVol/31 AS "Volume", "Whirl Pool" AS "Source"
    FROM kyla_ko AS q2, (SELECT @totVol := 0) AS d
    WHERE fv = 3 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE " 
{Root Container.Label 74.text}%"
UNION
    SELECT StartTime AS "StartTime", name AS "ProductCode", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume", "Whirl Pool" AS "Source" 
    FROM wp_ko AS q3
    WHERE fv = 3 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT StartTime AS "StartTime", srcName AS "ProductCode", srcLot AS "Lot", dest AS "FV", TotVol AS "Volume", CONCAT ('Rack From FV ',source) AS "Source"
    FROM rack AS q4
    WHERE dest = 3 AND destname = "{Root Container.Product Code Lbl.text}" AND destlot LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT StartTime AS "StartTime", srcName AS "ProductCode", srcLot AS "Lot", Source AS "FV", TotVol * -1 AS "Volume", CONCAT('Rack To FV ', dest) AS "Source"
    FROM rack as q5
    WHERE Source = 3 AND srcName = "{Root Container.Product Code Lbl.text}" AND srcLot LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT StartTime AS "StartTime", Name AS "ProductCode", Lot AS "Lot", fv AS "FV", Total_Bls * -1 AS "Volume", "Spin" AS "Source"
    FROM bt_runin as q6
    WHERE fv = 3 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT StartTime AS "StartTime", name AS "ProductCode", lot AS "Lot", "3" AS "FV", vol/31 AS "Volume", CONCAT('Diverted From knockout ', name," ", lot) AS "Source"
    FROM divert as q7
    WHERE fv = 3 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT t_stamp AS "StartTime", wortBrew AS "ProductCode", wortBatch AS "Lot", wortFV AS "FV", ((ActualPitch)/9.04)/31 AS "Volume", CONCAT('Yeast Pitch ',lablot) AS "Source"
    FROM harvest as q8
    WHERE wortfv = 3 AND wortBrew = "{Root Container.Product Code Lbl.text}" AND wortBatch LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT t_stamp AS "StartTime", srce1Brew AS "ProductCode", srce1Batch AS "Lot", srce1FV AS "FV", (((srce1wt)/9.04)/31)*-1 AS "Volume", CONCAT('Yeast Harvest ',srce1Lot) AS "Source"
    FROM harvest as q9
    WHERE srce1FV = 3 AND srce1Brew = "{Root Container.Product Code Lbl.text}" AND srce1Batch LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT t_stamp AS "StartTime", srce2Brew AS "ProductCode", srce2Batch AS "Lot", srce2FV AS "FV", (((srce2wt)/9.04)/31)*-1 AS "Volume", CONCAT('Yeast Harvest ',srce2Lot) AS "Source"
    FROM harvest as q10
    WHERE srce1FV = 3 AND srce1Brew = "{Root Container.Product Code Lbl.text}" AND srce1Batch LIKE "{Root Container.Label 74.text}%"
UNION
    SELECT StartTime AS "StartTime", name AS "ProductCode", lot AS "Lot", source AS "FV", (vol / 31)*-1 AS "Volume", type AS "Source"
    FROM dump as q11
    WHERE source = 3 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
    ) as q1
ORDER BY q1.StartTime ASC

    enter image description here