To find out the sum total of GRNQty and GRNValue of particular item from column 1

Question

Query 1 #

SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue
    FROM  suppdeldetail AS supdd LEFT OUTER JOIN  suppdelhead AS supdh ON supdd.GrnID=supdh.GrnID
    LEFT OUTER JOIN  suppliersitem AS supi ON supi.SupplierID=supdh.SupplierID
    LEFT OUTER JOIN  specificitem AS spi ON spi.Item=supdd.ItemID
    LEFT OUTER JOIN  itembrand AS iteb ON iteb.BrandID=supdd.BrandId
    WHERE (supdh.period BETWEEN '2014-12-01' AND '2014-12-09') AND supi.ContractNo='CN 00152A'
    GROUP BY spi.ItemName,supdd.PackageId,supdd.Qty

    UNION ALL

    SELECT spi.Item,spi.ItemName,supddh.PackageID,spi.Specifics,iteb.brandname,supddh.Qty AS GRNQty,supddh.GP AS UnitPrice,(supddh.Qty * Supddh.GP) AS GRNValue
    FROM  suppdeldetailhist AS supddh LEFT OUTER JOIN  suppdelheadhist AS supdhh ON supddh.GrnID=supdhh.GrnID
    LEFT OUTER JOIN  suppliersitem AS supih ON supih.SupplierID=supdhh.SupplierID
    LEFT OUTER JOIN  specificitem AS spi ON spi.Item=supddh.ItemID
    LEFT OUTER JOIN  itembrand AS iteb ON iteb.BrandID=supddh.BrandId
    WHERE (supdhh.period BETWEEN '2014-12-01' AND '2014-12-09') AND supih.ContractNo='CN 00152A'
    GROUP BY spi.ItemName,supddh.PackageId,supddh.Qty

After execution of query .The query output is like this below

Item           ItemName               PackageID       Specifics   brandname  GRNQty  UnitPrice GRNValue

102814742801    ALL BRAN FLAKES      1PKT X 375GM                  KELLOGS      50    15.9     795
102814742801    ALL BRAN FLAKES      1PKT X 375GM                  KELLOGS      200   15.9     3180
102814742801    ALL BRAN FLAKES      1PKT X 375GM                  KELLOGS      240   15.9     3816
102814742801    ALL BRAN FLAKES      1PKT X 375GM                  KELLOGS      300   15.9     4770
100314860301    ALMOND FLAKED        1PKT X 1KG                    HYSON        20    52       1040
100314860301    ALMOND FLAKED        1PKT X 1KG                    HYSON        30    52       1560
101426707397    ANCHOVY FILLETS      1TIN X 360GMS                 VARIOUS      12    34       408
101426707397    ANCHOVY FILLETS      1TIN X 360GMS                 VARIOUS      24    34       816
100126706163    ARTICHOKE HEART      1TIN X 425GMS                 VARIOUS      22.58 11.75    265.31
100126706163    ARTICHOKE HEART      1TIN X 425GMS                 VARIOUS      120   11.75    1410
100126706163    ARTICHOKE HEART      1TIN X 425GMS                 VARIOUS      225.88 11.75  2654.09
102914012905    BAHRAIN MIX MASALA   1KG X 1KG                     VARIOUS      7     34.75   243.25
102914012905    BAHRAIN MIX MASALA   1KG X 1KG                     VARIOUS      9.6   34.75   333.6
102914012905    BAHRAIN MIX MASALA   1KG X 1KG                     VARIOUS      11    34.75   382.25
102914012905    BAHRAIN MIX MASALA   1KG X 1KG                     VARIOUS      12    34.75   417
102914012905    BAHRAIN MIX MASALA   1KG X 1KG                     VARIOUS      20    34.75   695
101826991702    BAKING POWDER        1TIN X 100GM                  RIYADH FOODS 120   1.98    237.6
100126340153    BAMBOO SHOOT TIPS IN TIN1TIN X 540GM               NATCO        48    5.25    252
100126340153    BAMBOO SHOOT TIPS IN TIN1TIN X 540GM               NATCO        120   5.25    630

But i want the output of particular item, sum total of GRNQty & GRNValue with respect of particular item from column 1. Suppose that i want to sum total of of particular item (102814742801). So, as per my table output the GRNQty would be 790 and GRNValue would be 12561.

when i am grouping for that , to calculate the sum of GRNQty & GRNValue with Union all .

Query 2#

SELECT Item,ItemName,PackageID,Specifics,Qty,GP,BrandID,BrandName,Period,GrnID,supplierID,ContractNo
FROM
(
SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue
FROM  suppdeldetail AS supdd LEFT OUTER JOIN  suppdelhead AS supdh ON supdd.GrnID=supdh.GrnID
LEFT OUTER JOIN  suppliersitem AS supi ON supi.SupplierID=supdh.SupplierID
LEFT OUTER JOIN  specificitem AS spi ON spi.Item=supdd.ItemID
LEFT OUTER JOIN  itembrand AS iteb ON iteb.BrandID=supdd.BrandId
WHERE (supdh.period BETWEEN '2014-12-01' AND '2014-12-09') AND supi.ContractNo='CN 00152A'
GROUP BY spi.ItemName,supdd.PackageId,supdd.Qty

UNION ALL

SELECT spi.Item,spi.ItemName,supddh.PackageID,spi.Specifics,iteb.brandname,supddh.Qty AS GRNQty,supddh.GP AS UnitPrice,(supddh.Qty * Supddh.GP) AS GRNValue
FROM  suppdeldetailhist AS supddh LEFT OUTER JOIN  suppdelheadhist AS supdhh ON supddh.GrnID=supdhh.GrnID
LEFT OUTER JOIN  suppliersitem AS supih ON supih.SupplierID=supdhh.SupplierID
LEFT OUTER JOIN  specificitem AS spi ON spi.Item=supddh.ItemID
LEFT OUTER JOIN  itembrand AS iteb ON iteb.BrandID=supddh.BrandId
WHERE (supdhh.period BETWEEN '2014-12-01' AND '2014-12-09') AND supih.ContractNo='CN 00152A'
GROUP BY spi.ItemName,supddh.PackageId,supddh.Qty
)tmp
GROUP BY ItemName,PackageId,Qty
;

then it gives the error :

Query : SELECT Item,ItemName,PackageID,Specifics,Qty,GP,BrandID,BrandName,Period,GrnID,supplierID,ContractNo FROM ( SELECT spi.Item,spi....

Error Code : 1054 Unknown column 'Qty' in 'field list' Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000

NB:- UNION ALL is required because I am fetching the particular contract No from current table as well as also history table of MySQL Database. Any clue or hint will be appreciated.

Answer 1

To fix the error: You exposed Qty AS GRNQty; use the latter outside.

If there is a question about Q1, I missed it.

I recommend using some extra parens when doing UNION:

( SELECT ... )
UNION ALL
( SELECT ... )

Without these parens, you might add a clause (eg LIMIT) and have it mysteriously apply to the second SELECT instead of the UNION.