mysql query not working

i am working on a query which joins several tables.here's the code. the query works fine until the time i add the third line SUM(SaleItems_T.qtymajor) AS sales. i get an error message which says Unknown column 'SaleItems_T.qtymajor' in 'field list' I am trying to build an reorder worksheet.Help is much appreciated.

SELECT ProductMaster_T.ProductName_VC AS PGroup, 
StockMain_T.ItemDescription AS Item,
SUM(SaleItems_T.qtymajor) AS sales,
stockbuffers_T.buffer_qty AS BufferQty, 
(stkbalance_T.AJ1+stkbalance_T.AR2+stkbalance_T.AD3+stkbalance_T.DX4) AS Stock, 
(stkbalance_T.AJ1+stkbalance_T.AR2+stkbalance_T.AD3+stkbalance_T.DX4)-stockbuffers_T.buffer_qty AS Result
FROM ProductMaster_T, StockMain_T, stockbuffers_T, stkbalance_T
WHERE StockMain_T.ItemCode = stockbuffers_T开发者_Python百科.itemcode 
AND
StockMain_T.ItemCode = stkbalance_T.itemid
AND
ProductMaster_T.ProductID = StockMain_T.ProdID
AND
SaleItems_T.ItemID = StockMain_T.ItemCode
ORDER BY
ProductName_VC,ItemDescription ASC

You haven't referenced the SaleItems_T table in your query, either in the FROM clause, or through a JOIN.

This is where your query is wrong:

FROM ProductMaster_T, StockMain_T, stockbuffers_T, stkbalance_T

Change that to:

FROM ProductMaster_T, StockMain_T, stockbuffers_T, stkbalance_T, SaleItems_T

(Please no vote for this. I only put it here as comment space is not suitable fot such long comment.)

You should really use explicit JOIN ... ON join_condition syntax instead of the implicit JOIN via WHERE conditions (this is really old way to do it). It's better because it's hard to forget a condition (or a table, as you did!) and thus less error-prone. It also separates the join conditions (which you'll use in almost every query) from the other conditions you may have in various queries.

So, instead of

FROM ProductMaster_T, StockMain_T
WHERE ProductMaster_T.ProductID = StockMain_T.ProdID

write:

FROM ProductMaster_T
  JOIN StockMain_T
    ON ProductMaster_T.ProductID = StockMain_T.ProdID

It's also nice to use aliases (with the (optional) AS keyword). It make code more readable:

FROM ProductMaster_T AS p
  JOIN StockMain_T AS m
    ON p.ProductID = m.ProdID

The whole query could be written as:

SELECT
    master.ProductName_VC AS PGroup, 
    main.ItemDescription  AS Item,
    SUM(items.qtymajor)   AS sales,
    buf.buffer_qty        AS BufferQty, 
    (bal.AJ1 + bal.AR2 + bal.AD3 + bal.DX4)
      AS Stock, 
    (bal.AJ1 + bal.AR2 + bal.AD3 + bal.DX4) - buf.buffer_qty
      AS Result
FROM ProductMaster_T AS master
    JOIN StockMain_T AS main
        ON master.ProductID = main.ProdID
    JOIN stockbuffers_T AS buf
        ON main.ItemCode = buf.itemcode 
    JOIN stkbalance_T AS bal
        ON main.ItemCode = bal.itemid
    JOIN SaleItems_T AS items
        ON items.ItemID = main.ItemCode
ORDER BY
    ProductName_VC ASC,
    ItemDescription ASC  
GROUP BY  ??? main.ItemCode ???   --- depends on your tables'
                                  --- relationships