MYSQL Unknown Column in Query

Question

I am looking to fetch sum of transactions grouping them by item ID. I am able to get desired result fron below query in Oracle DB but the same does not work in MYSQL.

Below error is what I am getting:

Unknown column 'tran.itemid' in 'on clause'

SELECT tran.itemid, 
    (SELECT SUM(tranitm.quandity) 
        FROM transactions_item tranitm 
        JOIN transactions tr  
            ON(tr.id  = tranitm.transid and tranitm.itemid = tran.itemid) 
        where TR.TRANSTYPE = 1) Purchases, 
    (SELECT SUM(tranitm.quandity) 
        FROM transactions_item tranitm 
        JOIN transactions tr 
            ON(tr.id = tranitm.transid and tranitm.itemid = tran.itemid) 
        where TR.TRANSTYPE = 2 ) Sales, 
    (SELECT SUM(tranitm.quandity) 
        FROM transactions_item tranitm 
        JOIN transactions tr 
            ON(tr.id = tranitm.transid and tranitm.itemid = tran.itemid) 
        where TR.TRANSTYPE = 3 ) PurchasesRtn, 
    (SELECT SUM(tranitm.quandity) 
        FROM transactions_item tranitm 
        JOIN  transactions tr 
            ON(tr.id = tranitm.transid and tranitm.itemid = tran.itemid)
        where TR.TRANSTYPE = 4) SalesRtn 
FROM transactions_item tran 
GROUP BY tran.itemid

Schema :

CREATE TABLE transactions
( id INT, transtype INT,fromledger INT,
 ledgerid INT,agentid INT, quandity FLOAT,rate FLOAT);

CREATE TABLE transactions_item
 (transid INT, itemid INT,quandity FLOAT,rate FLOAT);

INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (1, 1, 1, 1, 1, 100, 1000);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (2, 2, 1, 1, 1, 10, 1200);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (3, 3, 1, 1, 1, 50, 1500);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (4, 4, 1, 1, 1, 120, 1300);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (5, 1, 1, 1, 1, 10, 1100);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (6, 2, 1, 1, 1, 12, 1400);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (7, 3, 1, 1, 1, 130, 1550);
INsert into transactions (id, transtype, fromledger, ledgerid, agentid, 
 quandity, rate) values (8, 4, 1, 1, 1, 110, 1700);

INsert into transactions_item (transid, itemid,quandity,rate) values (1, 1, 100, 1000);
INsert into transactions_item (transid, itemid,quandity,rate) values (2, 1, 10, 1200);
INsert into transactions_item (transid, itemid,quandity,rate) values (3, 1, 50, 1500);
INsert into transactions_item (transid, itemid,quandity,rate) values (4, 1, 120, 1300);
INsert into transactions_item (transid, itemid,quandity,rate) values (5, 1, 10, 1100);
INsert into transactions_item (transid, itemid,quandity,rate) values (6, 1, 12, 1400);
INsert into transactions_item (transid, itemid,quandity,rate) values (7, 1, 130, 1550);
INsert into transactions_item (transid, itemid,quandity,rate) values (8, 1, 110, 1700);
INsert into transactions_item (transid, itemid,quandity,rate) values (1, 2, 100, 1000);
INsert into transactions_item (transid, itemid,quandity,rate) values (2, 2, 10, 1200);
INsert into transactions_item (transid, itemid,quandity,rate) values (3, 2, 50, 1500);
INsert into transactions_item (transid, itemid,quandity,rate) values (4, 2, 120, 1300);
INsert into transactions_item (transid, itemid,quandity,rate) values (5, 2, 10, 1100);
INsert into transactions_item (transid, itemid,quandity,rate) values (6, 2, 12, 1400);
INsert into transactions_item (transid, itemid,quandity,rate) values (7, 2, 130, 1550);
INsert into transactions_item (transid, itemid,quandity,rate) values (8, 2, 110, 1700);

transactions is master table.....transactions_item is child............quandity and rate in master is the sum of all the entries in child table. Child table can have multiple item entires for same transaction id. — Deep, Nov 23 '17 at 11:09

Barmar · Answer 1 · 2017-11-23T11:09:37.027

1

The ON clauses in the subqueries should be used to relate the tables being joined. Conditions that aren't part of the join should be in WHERE.

But there's a better way to do this than all those correlated subqueries.

SELECT tran.itemid,
        SUM(IF(tr.transtype = 1, tran.quandity, 0)) AS Purchases,
        SUM(IF(tr.transtype = 2, tran.quandity, 0)) AS Sales,
        SUM(IF(tr.transtype = 3, tran.quandity, 0)) AS PurchasesRtn,
        SUM(IF(tr.transtype = 4, tran.quandity, 0)) AS SalesRtn
FROM transactions_item AS tran
JOIN transactions AS tr ON tran.transid = tr.id
GROUP BY tran.itemid

DEMO

See need to return two sets of data with two different where clauses

edited Nov 23 '17 at 11:09

answered Nov 23 '17 at 09:54

Barmar

741,623
53
500
612

Thank you for the above. But I am getting same result for all the items with the above. – Deep Nov 23 '17 at 10:00
Getting 1084 for all Purchases, Sales..........and for both items (1 & 2) – Deep Nov 23 '17 at 10:22
Forgot the `ON` clause – Barmar Nov 23 '17 at 10:35
I think it is self joining the table – Deep Nov 23 '17 at 10:38
I changed the above query to: SELECT tran.itemid, SUM(IF(tr.transtype = 1, tran.quandity, 0)) AS Purchases, SUM(IF(tr.transtype = 2, tran.quandity, 0)) AS Sales, SUM(IF(tr.transtype = 3, tran.quandity, 0)) AS PurchasesRtn, SUM(IF(tr.transtype = 4, tran.quandity, 0)) AS SalesRtn FROM transactions_item AS tran JOIN transactions AS tr ON tr.id = tran.transid GROUP BY tran.itemid ......................................now getting the correct result but it is repeating for both the item id's(1&2) – Deep Nov 23 '17 at 10:43
SELECT tran.itemid, SUM(IF(tr.transtype = 1, tran.quandity, 0)) AS Purchases, SUM(IF(tr.transtype = 2, tran.quandity, 0)) AS Sales, SUM(IF(tr.transtype = 3, tran.quandity, 0)) AS PurchasesRtn, SUM(IF(tr.transtype = 4, tran.quandity, 0)) AS SalesRtn FROM transactions_item AS tran JOIN transactions AS tr ON tr.id = tran.transid GROUP BY tran.itemid – Deep Nov 23 '17 at 10:44
now the only issue with above query is getting the correct count for individual items and I think this needs a self join.....not sure though – Deep Nov 23 '17 at 11:00
I'm getting the same totals for both items, but that's because your data has the same quandities for both. – Barmar Nov 23 '17 at 11:07
Like `(1, 1, 100, 1000)` and `(1, 2, 100, 1000)` – Barmar Nov 23 '17 at 11:08
Thank you Barmar......thats great help........sorry to ask but there is another addition to be made to the above response. I need to change the itemid to itemname and that exists in another table for which below is the schema: – Deep Nov 23 '17 at 11:41
CREATE TABLE item_dtl( id INT, name VARCHAR(50)); – Deep Nov 23 '17 at 11:41
I tried the below query to get itemname but getting same result for both the items – Deep Nov 23 '17 at 11:50
SELECT itm.name as itemname, SUM(IF(tr.transtype = 1, tran.quandity, 0)) AS Purchases, SUM(IF(tr.transtype = 2, tran.quandity, 0)) AS Sales, SUM(IF(tr.transtype = 3, tran.quandity, 0)) AS PurchasesRtn, SUM(IF(tr.transtype = 4, tran.quandity, 0)) AS SalesRtn FROM transactions_item AS tran JOIN item_dtl AS itm ON itm.id = tran.itemid JOIN transactions AS tr ON tr.id = tran.transid GROUP BY itemname – Deep Nov 23 '17 at 11:50
It works for me: http://www.sqlfiddle.com/#!9/254a46/2 Maybe you forgot to update the input data for this test. – Barmar Nov 23 '17 at 17:36

MYSQL Unknown Column in Query

1 Answers1