Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    14

    Unanswered: Sum Columns from multiple tables

    I have several tables that I need to sum up a colomn and display the results. I have been struggling with this for some time. Here are my Tables and their columns:

    Ingredients_tbl
    IngredientID
    Description

    Inventory_tbl
    IngredientID
    AmountRemaining
    Renconciled
    Active

    Order_Details_tbl
    IngredientID
    OrderAmount
    Status

    Order_Details_Details_tbl
    IngredientID
    OrderAmount
    Status

    I want to display all the ingredients from the ingredients table, sum the amountremaining of the items that are reconciled = 'false' from the inventory_tbl, sum the orderamount from both the order_details_tbl and Order_details_details_tbl for each ingredient based on status. Here is some sample data and results I am looking for:

    Ingredients_tbl
    IngredientID -- Description
    1 -- Corn
    2 -- Beer
    3 -- SBM
    4 -- Wine

    Inventory_tbl
    IngredientID -- AmountRemaining -- Reconciled
    1 -- 500 -- False
    1 -- 500 -- False
    1 -- 100 -- True
    1 -- 500 -- False
    2 -- 1000 -- False
    2 -- 1000 -- False
    4 -- 500 -- False
    4 -- 500 -- False

    Order_Details_tbl
    IngredientID -- OrderAmount -- Status
    1 -- 100 -- ORDERED
    1 -- 100 -- MIXED
    1 -- 100 -- DELIVERED
    2 -- 100 -- ORDERED
    3 -- 100 -- ORDERED
    3 -- 100 -- ORDERED
    3 -- 100 -- ORDERED
    4 -- 100 -- ORDERED
    4 -- 100 -- DELIVERED

    Order_Details_Details_tbl
    IngredientID -- OrderAmount -- Status
    1 -- 100 -- ORDERED
    1 -- 100 -- DELIVERED
    1 -- 100 -- DELIVERED
    2 -- 100 -- ORDERED
    3 -- 100 -- ORDERED
    3 -- 100 -- ORDERED
    4 -- 100 -- ORDERED

    I would like the results to be like this:
    IngredientID
    Description
    Inventory = Sum of AmountRemaining of the records in Inventory_tbl where Renconciled = 'false'
    Production1 = sum of OrderAmount of the records in the Order_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'
    Production2 = sum of OrderAmount of the records in the Order_Details_Details_tbl where Status = 'ORDERED' Or Status = 'MIXED'

    IngredientID -- Description -- Inventory -- Production1 -- Production2
    1 -- Corn -- 1500 -- 200 -- 100
    2 -- Beer -- 2000 -- 100 -- 100
    3 -- SBM -- 0 -- 300 -- 200
    4 -- Wine -- 1000 -- 100 -- 100

    I thought this was going to be as easy as a few simple joins and aggregate sum on the colomns like this:

    SELECT Ingredients_tbl.IngredientID, Ingredients_tbl.Description,
    Ingredients_tbl.LowInventory, SUM(Inventory_tbl.AmountRemaining) AS Inventory, SUM(Order_Details_tbl.OrderAmount) AS Production1, SUM(Order_Details_Details_tbl.OrderAmount) AS Production2
    FROM Ingredients_tbl
    LEFT OUTER JOIN Inventory_tbl ON Inventory_tbl.IngredientID = Ingredients_tbl.IngredientID AND Inventory_tbl.Reconciled = 'False'
    LEFT OUTER JOIN Order_Details_tbl ON Order_Details_tbl.IngredientID = Ingredients_tbl.IngredientID AND (Order_Details_tbl.Status = 'ORDERED' OR Order_Details_tbl.Status = 'MIXED')
    LEFT OUTER JOIN Order_Details_Details_tbl ON Order_Details_Details_tbl.IngredientID = Ingredients_tbl.IngredientID AND (Order_Details_Details_tbl.Status = 'ORDERED' OR Order_Details_Details_tbl.Status = 'MIXED')
    WHERE Ingredients_tbl.Active = 'True'
    GROUP BY Ingredients_tbl.ID, Ingredients_tbl.IngredientID, Ingredients_tbl.Description
    ORDER BY Ingredients_tbl.Description

    But this obviously has its issues with incorrect returned sum values. I am relitively new to sql and was wondering if I could get some help on getting the results I am looking for?

    Thanks,

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with 
        CTE_Inventory as
        (
            select IngredientID, SUM(AmountRemaining) as Inventory 
            from Inventory_tbl
            where Reconciled = 'False'
            group by IngredientID
        ),
        
        CTE_Details as
        (
            select IngredientID, SUM(OrderAmount) as Production1 
            from Order_Details_tbl
            where (Status = 'ORDERED') OR (Status = 'MIXED')
            group by IngredientID
        ),
        
        CTE_Details_Details as
        (
            select IngredientID, SUM(OrderAmount) as Production2 
            from Order_Details_Details_tbl
            where (Status = 'ORDERED') OR (Status = 'MIXED')
            group by IngredientID
        )
        
    SELECT 
        ig.IngredientID, 
        ig.Description,
        COALESCE(iv.Inventory, 0) AS Inventory,
        COALESCE(dt.Production1, 0) as Production1,
        COALESCE(dd.Production2, 0) as Production2
    FROM Ingredients_tbl as ig
    LEFT OUTER JOIN CTE_Inventory as iv ON iv.IngredientID = ig.IngredientID
    LEFT OUTER JOIN CTE_Details as dt ON dt.IngredientID = ig.IngredientID 
    LEFT OUTER JOIN CTE_Details_Details as dd ON dd.IngredientID = ig.IngredientID
    WHERE ig.Active = 'True'
    ORDER BY ig.Description
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    14
    Perfect!!! This is exactly what I was looking for. Thank You so much!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •