Wrong Aggregate Values

Transact-SQL
1

I am trying to get aggregate values from each table for each mix no.

Query as follows:

SELECT RawMaterialPreparation.Uniid, RawMaterialPreparation.MixNo, RawMaterialPreparation.MixQty, SUM(VacuumMixing.MixQty) AS VacuumQty,
SUM(MaterialFilling.FilledQty) AS FilledQty
FROM MaterialFilling LEFT OUTER JOIN
VacuumMixing ON MaterialFilling.VacuumMixId = VacuumMixing.Uniid RIGHT OUTER JOIN
RawMaterialPreparation ON VacuumMixing.MixId = RawMaterialPreparation.Uniid
GROUP BY RawMaterialPreparation.Uniid, RawMaterialPreparation.MixNo, RawMaterialPreparation.MixQty
ORDER BY RawMaterialPreparation.Uniid DESC

Note: Each mix no can have more than one transaction in vacuum mixing table and each vacuum mix each have more than one transaction in material filling table.

2

Please provide sample data and expected output (from your sample data).

3

Sample data:
Raw Material Preparation
ID - Mix No - Qty
26822 - 14757 - 600.00
Vacuum Mixing
ID - Vacuum No - Mix ID - Mix Qty
39036 - 2015070305 - 26822 - 400
39044 - 2015070313 - 26822 - 200
Material Filling
ID - Filling No - Vacuum ID - Vacuum Qty
84952 - 2015071007 - 39036 - 45.00
84953 - 2015071008 - 39036 - 30.00
84977 - 2015071032 - 39044 - 20.00

Expected output
Mix No - Mix Qty - Vacuum Qty - Filled Qty
14757 - 600 - 600 - 95

4

Try this:

select a.[Mix No]
      ,a.Qty
      ,sum(b.[Mix Qty]) as [Mix Qty]
      ,sum(b.[Vacuum Qty]) as [Vacuum Qty]
  from [Raw Material Preparation] as a
       left outer join (select b.[Mix ID]
                              ,b.[Mix Qty]
                              ,sum(c.[Vacuum Qty]) as [Vacuum Qty]
                          from [Vacuum Mixing] as b
                               left outer join [Material Filling] as c
                                            on c.[Vacuum ID]=b.ID
                         group by [Mix ID]
                                 ,[Mix Qty]
                       ) as b
                    on b.[Mix ID]=a.ID
 group by a.[Mix No]
         ,a.Qty