SQL Right Join on Non Unique

Question

I'm hoping that im over thinking this. but i need to sum a column where i have no unique link to join on and when i do it double ups columns.

This is my current SQL that works until i add the join on vwBatchInData then it doubles up every record, what is the best way to achieve this?

select b.fldBatchID as 'ID',SUM(bIn.fldBatchDetailsWeight)  as 'Batch In', sum(t.fldTransactionNetWeight) as 'Batch Out' , format((sum(t.fldTransactionNetWeight) / sum(bIn.fldBatchDetailsWeight)),'P2' ) as 'Yield'
    from [TRANSACTION] t 
        right join vwBatchInData bIn on bIn.fldBatchID = t.fldBatchID
        inner join Batch b on b.fldBatchID = t.fldBatchID
    where CAST(b.fldBatchDate as date) = '2020-03-04'
group by  b.fldBatchID**

vwBatchInData Table

    +------------+---------------+-----------------------+
    | fldBatchID | fldKillNumber | fldBatchDetailsWeight |
    +------------+---------------+-----------------------+
    |       2862 |        601598 | 164.40                |
    |       2862 |        601599 | 190.80                |
    |       2862 |        601596 | 195.00                |
    |       2862 |        601597 | 200.20                |
    |       2862 |        601594 | 176.60                |
    +------------+---------------+-----------------------+

Transaction Table

+------------+------------------+-------------------------+
| fldBatchID | fldTransactionID | fldTransactionNetWeight |
+------------+------------------+-------------------------+
|       2862 |         10242352 | 16.26                   |
|       2862 |         10242353 | 22.82                   |
|       2862 |         10242362 | 18.52                   |
|       2862 |         10242363 | 21.44                   |
|       2862 |         10242364 | 20.32                   |
+------------+------------------+-------------------------+

Batch Table

+------------+-------------------------+
| fldBatchID |      fldBatchDate       |
+------------+-------------------------+
|       2862 | 2020-03-04 00:00:00.000 |
+------------+-------------------------+

Desired output with the above snipets

+------+----------+-----------+---------+
|  ID  | Batch In | Batch Out |  Yield  |
+------+----------+-----------+---------+
| 2862 | 927.00   | 90.36     | 10.76 % |
+------+----------+-----------+---------+

user_1144035 · Answer

I think you just want to aggregate before joining:

select b.fldBatchID as ID,
       (bIn.fldBatchDetailsWeight)  as batch_in,
       (t.fldTransactionNetWeight) as batch_out, 
       format(t.fldTransactionNetWeight / bIn.fldBatchDetailsWeight, 'P2' ) as Yield
    from batch b left join
         (select bin.fldBatchID, sum(fldBatchDetailsWeight) as fldBatchDetailsWeight
          from vwBatchInData bin
          group by bin.fldBatchID
         ) bin
         on bIn.fldBatchID = b.fldBatchID left join
         (select t.fldBatchID, sum(fldTransactionNetWeight) as fldTransactionNetWeight
          from transactions t
          group by t.fldBatchID
         ) bin
         on t.fldBatchID = b.fldBatchID 
    where CAST(b.fldBatchDate as date) = '2020-03-04';

SQL Right Join on Non Unique

1 Answers