SUM and JOIN in SQL using SSMS (Quantity gets messed up)-CodePudding

I have an issue where when I added a join my quantity gets messed up. I joined the status table because I want to get the status from there. (Using SSMS

This is the correct code and the actual correct quantity:

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =         IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0

Correct code executed with correct quantity showing

Now when I try to join another table, I still want to get the same quantity shown above.

Here's what I tried but the quantity seems to have been multiplied by 9 or something. It could also be that my joins are wrong or I have a silly mistake.

Here's the new code (Joining the Part_Location table) but my quantity is incorrect (QTY should be the same as shown in image 1)

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  L.STATUS,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =     IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
INNER JOIN PART_LOCATION L ON L.PART_ID = TIT.PART_ID 
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
  AND L.STATUS = 'A'
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  L.STATUS
HAVING
  SUM(TIT.QTY) > 0

Alteration made where lines 6, 17, 21, and 32 where added but now QTY is incorrect

CodePudding user response：

You can use subquery instead of join. If you have more than one status for any L.PART_ID then the query will show error. you need to change the subquery according to your dbms.

For sql server:

select top 1STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'

for MySql:

select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A' Limit 1

Query:

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  (select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'),
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =         IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0