Sql Server Multiple Joins with Aggregate

Question

I'm trying to fix a bug in inherited code. This query is meant to bring back an amex_meal_amount_total of $33, but it is giving $99. The problem is with the second join - there are three associated items in the EE table that is making the aggregate sum up three rows.

SELECT ER.report_id,
       Isnull(Sum(EE_AMEX.meal_amount), 0) AS amex_meal_amount_total
FROM   expense_report ER (nolock)
       LEFT OUTER JOIN expense_expense EE_AMEX (nolock)
                    ON ER.report_id = EE_AMEX.report_id
                       AND EE_AMEX.line_item_type_id = 1
       LEFT OUTER JOIN expense_expense EE_OOP (nolock)
                    ON ER.report_id = EE_OOP.report_id
                       AND EE_OOP.line_item_type_id = 2
WHERE  er.report_id = 9733
GROUP  BY ER.report_id

It is clear to me that the developer was trying to use a table alias in the join (ex. EE_AMEX) to limit the sum function to the condition in the join.

There is only one row in the EE table with the line_item_type_id 1 for this ID. When I remove the other join statement, it brings back the expected $33.

 SELECT ER.report_id,
       Isnull(Sum(EE_AMEX.meal_amount), 0) AS amex_meal_amount_total
FROM   expense_report ER (nolock)
       LEFT OUTER JOIN expense_expense EE_AMEX (nolock)
                    ON ER.report_id = EE_AMEX.report_id
                       AND ee_oop.line_item_type_id = 1
WHERE  er.report_id = 9733
GROUP  BY ER.report_id

Is there a straightforward fix to this, or do I need to completely restructure the queries?

Table structure:

Trying to keep it simple for this question

expense_report:
report_id (PK)

expense_expense:
report_id (FK, one to many)
meal_amount (can be multiple rows with meal amounts per report_id)
taxi_amount (example of other charges)
line_item_type_id (1 is AMEX, 2 is OOP, can be any per row)

In this case the ER has one associated row in expense_expense with a meal charge of $33, so that was what I would expect.

However, there were three associated rows for other charges such as taxi, etc.

When running the query it sums it for three rows, thus the unexpected $99.

Thanks.

Why are you joining on oop when there is no usage of that table? — Giorgi Nakeuri, Mar 11 '15 at 04:24
The left join in first query is similar to ER.report_id = EE_AMEX.report_id AND (EE_OOP.line_item_type_id = 1 OR EE_OOP.line_item_type_id = 2). And you got the result in second query. — Jose Tuttu, Mar 11 '15 at 04:26
@ Giorgi Nakeuri, for sample purposes I didn't include the full query, it retrieves aggregates from the EE_OOP as well. Thanks — CutOffTies, Mar 11 '15 at 04:39
Can you provide the table structure, current result set and expected result set(not 99 and 33, but how result should look like)? — Jose Tuttu, Mar 11 '15 at 08:54

score 1 · Accepted Answer · edited Mar 11 '15 at 05:50

How about moving the sum into a subquery instead? You will probably need to do the same for your EE_OOP aggregate, but I wasn't sure what you were pulling out of that.

 SELECT ER.report_id,
       Isnull((SELECT Sum(meal_amount)
               FROM   expense_expense EE_AMEX (nolock)
               WHERE  EE_AMEX.report_id = ER.report_id
                      AND EE_AMEX.line_item_type_id = 1), 0) AS
       amex_meal_amount_total
FROM   expense_report ER (nolock)
       LEFT OUTER JOIN expense_expense EE_OOP (nolock)
                    ON ER.report_id = EE_OOP.report_id
                       AND EE_OOP.line_item_type_id = 2
WHERE  er.report_id = 9733

score 1 · Answer 2 · answered Mar 11 '15 at 13:09

If you are seeking why the first query returned $99, lets see how. let tables be defined as

select 1 report_id into #expense_report;

select * into #expense_expense from (
select 1 report_id, 33 meal_amount, 0 taxi_amount, 1 line_item_type_id
union all
select 1 report_id, 0 meal_amount, 33 taxi_amount, 2 line_item_type_id
union all
select 1 report_id, 0 meal_amount, 33 taxi_amount, 2 line_item_type_id) t;

So after first left join with expense table the result will be a single row

SELECT *
FROM   #expense_report ER (nolock)
       LEFT OUTER JOIN #expense_expense EE_AMEX (nolock)
                    ON ER.report_id = EE_AMEX.report_id
                       AND EE_AMEX.line_item_type_id = 1
WHERE  er.report_id = 1;

report_id   report_id   meal_amount taxi_amount line_item_type_id
1               1         33            0             1

Now the second left join will be applied on this result, ie, a single row join to a double row result, which will result in 2 rows.

SELECT *
FROM   #expense_report ER (nolock)
       LEFT OUTER JOIN #expense_expense EE_AMEX (nolock)
                    ON ER.report_id = EE_AMEX.report_id
                       AND EE_AMEX.line_item_type_id = 1
       LEFT OUTER JOIN #expense_expense EE_OOP (nolock)
                    ON ER.report_id = EE_OOP.report_id
                       AND EE_OOP.line_item_type_id = 2
WHERE  er.report_id = 1;

report_id   report_id   meal_amount taxi_amount line_item_type_id   report_id   meal_amount taxi_amount line_item_type_id
1   1   33  0   1   1   0   33  2
1   1   33  0   1   1   0   33  2

Note the columns. The first table's meal_amount repeats because it is joining with 2 rows of the right table. So taking sum on this will result in $66 not $33.

If you want to show both taxi and meal amount in a single row but different column, then use below query:

SELECT ER.report_id,
       Isnull(Sum(case when EE_AMEX.line_item_type_id  =1 then EE_AMEX.meal_amount end), 0) AS amex_meal_amount_total,
       Isnull(Sum(case when EE_AMEX.line_item_type_id  =2 then EE_AMEX.taxi_amount end), 0) AS amex_taxi_amount_total
FROM   #expense_report ER (nolock)
       LEFT OUTER JOIN #expense_expense EE_AMEX (nolock)
                    ON ER.report_id = EE_AMEX.report_id
WHERE  er.report_id = 1
GROUP  BY ER.report_id

Sql Server Multiple Joins with Aggregate

2 Answers2