1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.