Joining Another Table in Select Statement Yields Incorrect Higher Aggregate Totals

Question

Straight forward aggregate select statement I am having trouble with.

When I first run this query, no problems:

SELECT distinct 

t2.primary_insurance [Primary Insurance]
,COUNT(t2.account_number) [Volume]  
,SUM(t2.los) [Total LOS] 
,AVG(t2.los) [Avg LOS]  
,AVG(t1.drg_cost_weight) [Avg CMI] 
,sum(t2.total_charges) [Total Charges]
,sum(-t2.insurance_receipts) [Total Receipts]   

FROM 
[table1] t1
LEFT OUTER join
[table2] t2
on t1.account_number = t2.account_number

GROUP BY
t2.primary_insurance

order by 
[Primary Insurance]

But as soon as I add Table3 to aggregate it's data, it returns a different [Volume] total which makes all the other totals incorrect.

SELECT distinct 

t2.primary_insurance [Primary Insurance]
,COUNT(t2.account_number) [Volume]  
,SUM(t2.los) [Total LOS] 
,AVG(t2.los) [Avg LOS]  
,AVG(t1.drg_cost_weight) [Avg CMI] 
,sum(t2.total_charges) [Total Charges]
,sum(-t2.insurance_receipts) [Total Receipts]
,sum(t3.[direct_cost]) [Direct Cost]
,sum(t3.[indirect_cost])[Indirect Cost]
,sum((t3.[direct_cost] + t3.[indirect_cost])) [Total Cost]
,sum((-t2.insurance_receipts - t3.[direct_cost])) [Contribution Margin] 
,sum((-t2.insurance_receipts - (t3.[direct_cost] + CR.[indirect_cost]))) [Profit]

from
[table1] t1
LEFT OUTER join
[table2] t2
on t1.account_number = t2.account_number
JOIN
[table3] t3
on t2.[account_number]  = t3.[account_number]

GROUP BY
t2.primary_insurance

order by 
[Primary Insurance]

I've tried joining different ways and but keep getting the same inflated volume total and cannot I get the volume total from the original query (the correct volume total). To be clear, the problem is the second query gives me a volume total that is higher than volume total from the first query. The higher volume total gives me higher totals for everything else.

I've also tried using a subquery for the table3 data but cannot get that right either.

The two queries are identical except with the addition of table3 and summing various data from that table. The query doesn't error out, just gives incorrect total.

Using SQL server 2008

Any input or suggestions are greatly appreciated!

Are you sure you want to join t3.account_number with t2.account_number. — DevelopmentIsMyPassion, Apr 11 '13 at 18:07

score 0 · Accepted Answer · answered Apr 11 '13 at 18:14

Then it's pretty clear that you are duplicating values on table3, meaning that account_number is not a primary key on that table (nor is unique). You either join with the key (or the set of columns that are unique on that table, or you do the aggregation on that table beforehand:

SELECT  t2.primary_insurance [Primary Insurance]
        ,COUNT(t2.account_number) [Volume]  
        ,SUM(t2.los) [Total LOS] 
        ,AVG(t2.los) [Avg LOS]  
        ,AVG(t1.drg_cost_weight) [Avg CMI] 
        ,SUM(t2.total_charges) [Total Charges]
        ,SUM(-t2.insurance_receipts) [Total Receipts]
        ,SUM(t3.[direct_cost]) [Direct Cost]
        ,SUM(t3.[indirect_cost])[Indirect Cost]
        ,SUM((t3.[direct_cost] + t3.[indirect_cost])) [Total Cost]
        ,SUM((-t2.insurance_receipts - t3.[direct_cost])) [Contribution Margin] 
        ,SUM((-t2.insurance_receipts - (t3.[direct_cost] + CR.[indirect_cost]))) [Profit]
FROM [table1] t1 
LEFT JOIN [table2] t2
    ON t1.account_number = t2.account_number
LEFT JOIN ( SELECT  [account_number], 
                    SUM([direct_cost]) [direct_cost],
                    SUM([indirect_cost]) [indirect_cost]
            FROM [table3]
            GROUP BY [account_number]) t3
    ON t2.[account_number]  = t3.[account_number]
GROUP BY t2.primary_insurance
ORDER BY [Primary Insurance]

Joining Another Table in Select Statement Yields Incorrect Higher Aggregate Totals

1 Answers1