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

    Unanswered: Aggregate SUM from multiple tables, and Grouping

    I have several tables that I need to summarize data from two tables based upon a dates passed in and group that data. I have attached my table layout, some sample data, and how I would like the results to look.

    Any suggestions are greatly appreciated.

    Thanks in advance,
    Jad
    Attached Files Attached Files

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

    Code:
    with CTE as
    (    
        select 
            o.PlantID, 
            d.IngredientID, 
            SUM(d.OrderAmount) as OrderAmount, 
            SUM(d.BatchedAmount) as BatchedAmount
        from Orders_tbl as o
        left join Order_Details_tbl as d on d.OrderNumber = o.OrderNumber
        --where o.TimeInMix between @BeginDate and @EndDate and ...
        group by o.PlantID, d.IngredientID
    
        union all
    
        select 
            o.PlantID, 
            d.IngredientID, 
            SUM(d.OrderAmount) as OrderAmount, 
            SUM(d.BatchedAmount) as BatchedAmount
        from Orders_tbl as o
        left join Order_Details_Details_tbl as d on d.OrderNumber = o.OrderNumber
        --where o.TimeInMix between @BeginDate and @EndDate and ...
        group by o.PlantID, d.IngredientID
    )
        
    select 
        p.PlantID, 
        i.IngredientID, 
        SUM(c.OrderAmount) as OrderAmount, 
        SUM(c.BatchedAmount) as BatchedAmount
    from CTE as c
    join Plants_tbl as p on p.ID = c.PlantID
    join Ingredients_tbl as i on i.ID = c.IngredientID
    group by p.PlantID, i.IngredientID
    Hope this helps.

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

    Thanks again,
    Jad

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
  •