-1

I have 2 queries;

  • 1 with sub query join - this is pulling everything back correctly
  • 1 with a join - this is pulling back an incorrect calculation in the GrossAnnualDebit, and overall is much lower than the GrossAnnualDebit figure from the sub query joins.
SELECT prty_id AS PropertyID, 
       ISNULL(SUM(tr.grs_val_trans), 0) + ISNULL(SUM(voi.grs_valtrs), 0) AS GrossAnnualDebit
FROM qlfdat..hgmprty1 p1
     LEFT JOIN
(
    SELECT prty_ref, 
           SUM(grs_val_trans) AS grs_val_trans
    FROM qlfdat..hratrans
    WHERE trans_ppyy BETWEEN 201805 AND 201904
          AND trans_type = 'D'
    GROUP BY prty_ref
) AS tr ON tr.prty_ref = p1.prty_id
     LEFT JOIN
(
    SELECT prty_ref, 
           SUM(grs_valtrs) AS grs_valtrs
    FROM qlfdat..hraptvtt
    WHERE trans_ppyy BETWEEN 201805 AND 201904
    GROUP BY prty_ref
) AS voi ON voi.prty_ref = p1.prty_id
GROUP BY prty_id;

SELECT prty_id AS PropertyID, 
       ISNULL(SUM(tr.grs_val_trans), 0) + ISNULL(SUM(voi.grs_valtrs), 0) AS GrossAnnualDebit
FROM qlfdat..hgmprty1 p1
     LEFT JOIN qlfdat..hratrans AS tr ON tr.prty_ref = p1.prty_id
                                         AND tr.trans_type = 'D'
                                         AND tr.trans_ppyy BETWEEN 201805 AND 201904
     LEFT JOIN qlfdat..hraptvtt AS voi ON voi.prty_ref = p1.prty_id
                                          AND voi.trans_ppyy BETWEEN 201805 AND 201904
                                  AND voi.trans_ppyy = tr.trans_ppyy
GROUP BY prty_id;
WRD299
  • 37
  • 11
  • 1
    We can't see your data, or run your queries, so this is very hard to even begin to answer on. Note, however, that you have a `GROUP BY` and `SUM` in the subqueries, however, the only aggregation in your second query is the once. That changes the query, so those 2 queries are not the same. – Thom A May 09 '19 at 09:22
  • Thanks @Larnu - I appreciate they're not the same, however they're both pulling back very similar data. I also appreciate that you cannot see the data. Is there anyway I can represent data onto Stackoverflow? – WRD299 May 09 '19 at 09:25
  • I believe the data difference stems from the non-equivalence of putting the same condition in the `ON` and `WHERE` parts of a `LEFT JOIN`. Not sure how to formulate an example, though. – George Menoutis May 09 '19 at 09:32
  • 1
    Yes @WRD299, DDL and DML statements are the best way, as we can run them on our local instance. – Thom A May 09 '19 at 10:13
  • 1
    This seems likely to be a common error where people want the join of some aggregations (each possibly involving joining) but they erroneously try to do all the joining then all the aggregating. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) PS Of course your queries don't agree. What are we supposed to say?--Find out how the operators act? If you think they should be the same then explain why & show the first step you get something you don't expect so we can tell you where your misunderstandings are. – philipxy May 10 '19 at 08:59
  • Thank you @philipxy I will do this for future questions :) – WRD299 May 13 '19 at 11:35

1 Answers1

1

I could tell exactly what's the issue without sample code, but a difference I could see is on second query, your voi table is no longer left join with p1 table, you left join it with tr table, that might cause your issue.

Verse He
  • 97
  • 9