I have a query where we have UNION several tables into one Table Component.
We now want to get a Balance of one of these columns.
Here is a pic of the Table results without the SUM() function.
Here is my query with the SUM() functions added.
Here is the Resulting Table
Below is the Table Customizer just in case there is an issue there.
Any help is appreciated.
Try leaving your sum column out of the union, wrap the entire union as a subquery, and do the sum in the outer query.
SELECT SUM(A.BALANCE)
FROM ( SUBQUERY as BALANCE) A
Could you guys show me the syntax. I am a Rookie when it comes to MySQL.
Paste your original SQL in a code block instead of a screen shot. So I can cut and paste. Be sure to use triple-backquotes above and below the code so it formats neatly.
SELECT StartTime AS "Start Time", name AS "Product Code", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume", SUM( koVol/31) AS "Balance"
FROM wp_ko
WHERE fv = 15 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", Name AS "Product Code", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume", SUM(koVol/31) AS "Balance"
FROM kyla_ko
WHERE fv = 15 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", destName AS "Product Code", destLot AS "Lot", dest AS "FV", TotVol AS "Volume", SUM(TotVol) AS "Balance"
FROM rack
WHERE dest = 15 AND destname = "{Root Container.Product Code Lbl.text}" AND destlot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", srcName AS "Product Code", srcLot AS "Lot", Source AS "FV", TotVol * -1 AS "Volume", SUM( TotVol * -1) AS "Balance"
FROM rack
WHERE Source = 15 AND srcName = "{Root Container.Product Code Lbl.text}" AND srcLot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", Name AS "Product Code", Lot AS "Lot", fv AS "FV", Total_Bls * -1 AS "Volume", SUM(Total_Bls * -1) AS "Balance"
FROM bt_runin
WHERE fv = 15 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
Try this:
Select StartTime AS "Start Time", name AS "Product Code", Lot, fv AS "FV", "Volume", sum("Volume") As "Balance"
FROM (
SELECT StartTime, name, Lot, fv, koVol/31 AS "Volume"
FROM wp_ko
WHERE fv = 15 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime, Name AS name, Lot, fv, koVol/31 AS "Volume"
FROM kyla_ko
WHERE fv = 15 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime, destName AS name, destLot AS "Lot", dest AS fv, TotVol AS "Volume"
FROM rack
WHERE dest = 15 AND destname = "{Root Container.Product Code Lbl.text}" AND destlot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime, srcName AS name, srcLot AS "Lot", Source AS fv, TotVol * -1 AS "Volume"
FROM rack
WHERE Source = 15 AND srcName = "{Root Container.Product Code Lbl.text}" AND srcLot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime, Name AS name, Lot, fv, Total_Bls * -1 AS "Volume"
FROM bt_runin
WHERE fv = 15 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
) SubQ
pturmel,
Below is what my query and below that is the error message I’m getting.
I checked all the tables and StartTime is valid column in each table.
SELECT StartTime AS "Start Time", name AS "Product Code", Lot AS "Lot", fv AS "FV", "Volume", SUM("Volume") AS "Balance"
FROM (
SELECT StartTime AS "Start Time", name AS "Product Code", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume"
FROM wp_ko
WHERE fv = 15 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", Name AS "Product Code", Lot AS "Lot", fv AS "FV", koVol/31 AS "Volume"
FROM kyla_ko
WHERE fv = 15 AND name = "{Root Container.Product Code Lbl.text}" AND lot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", destName AS "Product Code", destLot AS "Lot", dest AS "FV", TotVol AS "Volume"
FROM rack
WHERE dest = 15 AND destname = "{Root Container.Product Code Lbl.text}" AND destlot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", srcName AS "Product Code", srcLot AS "Lot", Source AS "FV", TotVol * -1 AS "Volume"
FROM rack
WHERE Source = 15 AND srcName = "{Root Container.Product Code Lbl.text}" AND srcLot LIKE "{Root Container.Label 74.text}%"
UNION
SELECT StartTime AS "Start Time", Name AS "Product Code", Lot AS "Lot", fv AS "FV", Total_Bls * -1 AS "Volume"
FROM bt_runin
WHERE fv = 15 AND Name = "{Root Container.Product Code Lbl.text}" AND Lot LIKE "{Root Container.Label 74.text}%"
) SubQ
Exception: Error running query:
SQLQuery(query=SELECT StartTime AS “Start Time”, name AS “Product Code”, Lot AS “Lot”, fv AS “FV”, “Volume”, SUM(“Volume”) AS “Balance”
FROM (
SELECT StartTime AS “Start Time”, name AS “Product Code”, Lot AS “Lot”, fv AS “FV”, koVol/31 AS “Volume”
FROM wp_ko
WHERE fv = 15 AND name = “kyla” AND lot LIKE “1010%”
UNION
SELECT StartTime AS “Start Time”, Name AS “Product Code”, Lot AS “Lot”, fv AS “FV”, koVol/31 AS “Volume”
FROM kyla_ko
WHERE fv = 15 AND name = “kyla” AND lot LIKE “1010%”
UNION
SELECT StartTime AS “Start Time”, destName AS “Product Code”, destLot AS “Lot”, dest AS “FV”, TotVol AS “Volume”
FROM rack
WHERE dest = 15 AND destname = “kyla” AND destlot LIKE “1010%”
UNION
SELECT StartTime AS “Start Time”, srcName AS “Product Code”, srcLot AS “Lot”, Source AS “FV”, TotVol * -1 AS “Volume”
FROM rack
WHERE Source = 15 AND srcName = “kyla” AND srcLot LIKE “1010%”
UNION
SELECT StartTime AS “Start Time”, Name AS “Product Code”, Lot AS “Lot”, fv AS “FV”, Total_Bls * -1 AS “Volume”
FROM bt_runin
WHERE fv = 15 AND Name = “kyla” AND Lot LIKE “1010%”
) SubQ, database=history)@5000ms
On: FV15Details.Root Container.Ledger Group.Table 1.data
caused by GatewayException: Unknown column ‘StartTime’ in ‘field list’
caused by MySQLSyntaxErrorException: Unknown column ‘StartTime’ in ‘field list’
Ignition v7.9.4 (b2017082911)
Java: Oracle Corporation 1.8.0_181
Since the columns have already been renamed, eg StartTime become ‘Start Time’, then the first SELECT statement should reference the new name and you dont need the xxx AS “xyz abc”.
Try modifying first select to something like:
SELECT “Start Time”, “Product Code”, …
What about the columns who’s names don’t exactly match?
Each inner query uses the raw column names. The first of those in the union sets the union’s output column names. The outer query uses the union’s output column names and sets the final column names.
Thank you all for your help. A friend of mine came up with the query below to get the desired table.
Below the query I attached a pic of one of the resulting tables.
Thanks again.
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
That would be a good friend! 
