Get sum of different column of different table in a single query

Question

I am getting wrong sum answer by this query - does anyone have any idea what I'm doing wrong? I am getting the first sum twice, the second sum 3 times more than the actual value and so on.

SELECT ogr.Region_ID,
       ogr.Region_Name,
       Sum(daf.Total_Allowance) AS DailyAllowance,
       Sum(naf.Total_Allowance) AS NightAllowance,
       Sum(caf.Total_Amount) AS AdvanceCash,
       Sum(att.Extra_Amounts) AS OverTime,
       Sum(eef.Total_Amount) AS EmployeeExpense,
       Sum(maf.Total_Amount) AS MobileAllowance,
       Sum(odf.On_Week_Amount) AS OnWeek,
       Sum(emp.Salary) AS EmployeeSalary
FROM   ORG_Region ogr
       LEFT OUTER JOIN DailyAllowanceForm AS daf
                    ON daf.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN NightAllowance AS naf
                    ON naf.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN CashAdvance AS caf
                    ON caf.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN Attandence AS att
                    ON att.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN EmployeeExpensesForm AS eef
                    ON eef.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN MobileAllowance AS maf
                    ON maf.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN OnDutyForms AS odf
                    ON odf.Region_ID = ogr.Region_ID
       LEFT OUTER JOIN Employee AS emp
                    ON emp.Region = ogr.Region_ID
GROUP  BY ogr.Region_ID,ogr.Region_Name

Answer 1

The problem is you are adding from duplicated rows:

TableA

Aid, Avalue
1, 7
2, 3

TableB

Aid
1
1
2
2
2

you ill get sums like 14 and 9 since you are counting B multiple times.

split that big select in sub queries like in Prashant answer

Answer 2

Try using INNER JOIN

SELECT ogr.Region_ID,
       ogr.Region_Name,
       Sum(daf.Total_Allowance) AS DailyAllowance,
       Sum(naf.Total_Allowance) AS NightAllowance,
       Sum(caf.Total_Amount) AS AdvanceCash,
       Sum(att.Extra_Amounts) AS OverTime,
       Sum(eef.Total_Amount) AS EmployeeExpense,
       Sum(maf.Total_Amount) AS MobileAllowance,
       Sum(odf.On_Week_Amount) AS OnWeek,
       Sum(emp.Salary) AS EmployeeSalary
FROM   ORG_Region ogr
       INNER JOIN DailyAllowanceForm AS daf
                ON daf.Region_ID = ogr.Region_ID
       INNER JOIN NightAllowance AS naf
                ON naf.Region_ID = ogr.Region_ID
       INNER JOIN CashAdvance AS caf
                ON caf.Region_ID = ogr.Region_ID
       INNER JOIN Attandence AS att
                ON att.Region_ID = ogr.Region_ID
       INNER JOIN EmployeeExpensesForm AS eef
                ON eef.Region_ID = ogr.Region_ID
       INNER JOIN MobileAllowance AS maf
                ON maf.Region_ID = ogr.Region_ID
       INNER JOIN OnDutyForms AS odf
                ON odf.Region_ID = ogr.Region_ID
       INNER JOIN Employee AS emp
                ON emp.Region = ogr.Region_ID
   GROUP  BY ogr.Region_ID,ogr.Region_Name

Another Way is

SELECT  ogr.Region_ID ,
    ogr.Region_Name ,
    ( SELECT    ISNULL(( SUM(daf.Total_Allowance) ), 0)
      FROM      DailyAllowanceForm AS daf
      WHERE     daf.Region_ID = ogr.Region_ID
    ) AS DailyAllowance ,
    ( SELECT    ISNULL(( SUM(naf.Total_Allowance) ), 0)
      FROM      NightAllowance AS naf
      WHERE     naf.Region_ID = ogr.Region_ID
    ) AS NightAllowance ,
    ( SELECT    ISNULL(( SUM(caf.Total_Amount) ), 0)
      FROM      CashAdvance AS caf
      WHERE     caf.Region_ID = ogr.Region_ID
    ) AS AdvanceCash ,
    ( SELECT    ISNULL(( SUM(att.Extra_Amounts) ), 0)
      FROM      Attandence AS att
      WHERE     att.Region_ID = ogr.Region_ID
    ) AS OverTime ,
    ( SELECT    ISNULL(( SUM(eef.Total_Amount) ), 0)
      FROM      EmployeeExpensesForm AS eef
      WHERE     eef.Region_ID = ogr.Region_ID
    ) AS EmployeeExpense ,
    ( SELECT    ISNULL(( SUM(maf.Total_Amount) ), 0)
      FROM      MobileAllowance AS maf
      WHERE     maf.Region_ID = ogr.Region_ID
    ) AS MobileAllowance ,
    ( SELECT    ISNULL(( SUM(odf.On_Week_Amount) ), 0)
      FROM      OnDutyForms AS odf
      WHERE     odf.Region_ID = ogr.Region_ID
    ) AS OnWeek ,
    ( SELECT    ISNULL(( SUM(emp.Salary) ), 0)
      FROM      Employee AS emp
      WHERE     emp.Region_ID = ogr.Region_ID
    ) AS EmployeeSalary
 FROM    ORG_Region ogr
 GROUP BY ogr.Region_ID ,
 ogr.Region_Name

Answer 3

The clean solution is using CTE, cook aggregate values then join it:

;WITH daf as (
   SELECT Region_ID, SUM(Total_Allowance) as DailyAllowance 
   FROM DailyAllowanceForm GROUP BY Region_ID
), naf as (
   SELECT Region_ID, SUM(Total_Allowance) as NightAllowance 
   FROM NightAllowance GROUP BY Region_ID
), caf as (
  ...
) 
SELECT ogr.Region_ID, ogr.Region_Name, 
       coalesce(daf.DailyAllowance, 0) AS DailyAllowance,
       coalesce(naf.NightAllowance, 0) AS NightAllowance,
       ...
FROM
   ORG_Region ogr 
LEFT OUTER JOIN 
   daf               ON daf.Region_ID = ogr.Region_ID
LEFT OUTER JOIN  
   naf               ON naf.Region_ID = ogr.Region_ID
   ...
GROUP BY ogr.Region_ID,ogr.Region_Name

Answer 4

Try this:

SELECT ogr.Region_ID, ogr.Region_Name, ISNULL(daf.DailyAllowance, 0) AS DailyAllowance, ISNULL(naf.NightAllowance, 0) AS NightAllowance, 
       ISNULL(caf.AdvanceCash, 0) AS AdvanceCash, ISNULL(att.OverTime, 0) AS OverTime, ISNULL(eef.EmployeeExpense, 0) AS EmployeeExpense, 
       ISNULL(maf.MobileAllowance, 0) AS MobileAllowance, ISNULL(odf.OnWeek, 0) AS OnWeek, ISNULL(emp.EmployeeSalary, 0) AS EmployeeSalary
FROM   ORG_Region ogr
LEFT OUTER JOIN (SELECT Region_ID, SUM(Total_Allowance) DailyAllowance FROM DailyAllowanceForm GROUP BY Region_ID) AS daf ON daf.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(Total_Allowance) NightAllowance FROM NightAllowance GROUP BY Region_ID) AS naf ON naf.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(Total_Amount) AS AdvanceCash FROM CashAdvance GROUP BY Region_ID) AS caf ON caf.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(Extra_Amounts) AS OverTime FROM Attandence GROUP BY Region_ID) AS att ON att.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(Total_Amount) AS EmployeeExpense FROM EmployeeExpensesForm GROUP BY Region_ID) AS eef ON eef.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(Total_Amount) AS MobileAllowance FROM MobileAllowance GROUP BY Region_ID) AS maf ON maf.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(On_Week_Amount) AS OnWeek FROM OnDutyForms GROUP BY Region_ID) AS odf ON odf.Region_ID = ogr.Region_ID
LEFT OUTER JOIN (SELECT Region_ID, SUM(Salary) AS EmployeeSalary FROM Employee GROUP BY Region_ID) AS emp ON emp.Region = ogr.Region_ID;