SUM and JOIN 2 columns with different tables but it duplicates results

Not Sure, how your tables are interlinked, but your JOIN query doesn't look correct.

Suppose you "TransferShipmentLine" table look like this

and second table "TransSalesEntry" looks like this

Then you can JOIN them and get SUM for column "QTY" using code SQL query as below

SELECT 
    [GroupBy1].[K2] AS [ItemNo], 
    [GroupBy1].[K1] AS [TransferCode], 
    [GroupBy1].[K3] AS [VariantCode], 
    [GroupBy1].[A1] AS [Qty]
    FROM ( SELECT 
        [TransferShipmentLine].[TransferCode] AS [K1], 
        [TransferShipmentLine].[ItemNo] AS [K2], 
        [TransferShipmentLine].[VariantCode] AS [K3], 
        SUM([TransferShipmentLine].[Qty]) AS [A1]
        FROM  [dbo].[TransferShipmentLine] AS [TransferShipmentLine]
        INNER JOIN [dbo].[TransSalesEntry] AS [TransSalesEntry] ON [TransferShipmentLine].[TransSalesId] = [TransSalesEntry].[Id]
        WHERE ('LOCATION1' = [TransSalesEntry].[StoreNo]) AND ('ITEM01' = [TransSalesEntry].[ItemNo]) AND ('VARIANT01' = [TransSalesEntry].[VariantCode])
        GROUP BY [TransferShipmentLine].[TransferCode], [TransferShipmentLine].[ItemNo], [TransferShipmentLine].[VariantCode]
    )  AS [GroupBy1]

Sample Output

You are not checking SQL query correctly, there is no "SELECT" statement inside your group by

SELECT 
    [GroupBy1].[K2] AS [ItemNo], 
    [GroupBy1].[K1] AS [TransferCode], 
    [GroupBy1].[K3] AS [VariantCode], 
	 [GroupBy1].[K4] AS [Description], 
    [GroupBy1].[A1] AS [Qty]
    FROM ( SELECT 
        [TransferShipmentLine].[TransferCode] AS [K1], 
        [TransferShipmentLine].[ItemNo] AS [K2], 
        [TransferShipmentLine].[VariantCode] AS [K3], 
		  [TransferShipmentLine].[Description] AS [K4], 
        SUM([TransferShipmentLine].[Qty]) AS [A1]
        FROM  [dbo].[TransferShipmentLine] AS [TransferShipmentLine]
      
INNER JOIN [TransSalesEntry] AS [TransSalesEntry]
ON
[TransferShipmentLine].[TransferCode] = [TransSalesEntry].StoreNo AND
[TransferShipmentLine].ItemNo = [TransSalesEntry].ItemNo AND
[TransferShipmentLine].VariantCode = [TransSalesEntry].VariantCode
        WHERE ('LOCATION1' = [TransSalesEntry].[StoreNo]) AND ('ITEM01' = [TransSalesEntry].[ItemNo]) AND ('VARIANT01' = [TransSalesEntry].[VariantCode])
        GROUP BY [TransferShipmentLine].[TransferCode], [TransferShipmentLine].[ItemNo], [TransferShipmentLine].[VariantCode], [TransferShipmentLine].[Description]
    )  AS [GroupBy1]

Modified your Query by changing column and table name as it is returning correct output as needed.

thank you for your reply

here's the sample data tables;

Transfer Shipment table;

Loc Code   -   Item No   -   Description   -   Description2   -   Variant Code   -   Qty
Location1       Item01         Active                Black                   Variant01             1
Location1       Item01         Active                Black                   Variant01             2
Location1       Item01         Active                Black                   Variant02             1
Location1       Item01         Active                Black                   Variant02             1

Sales Entry table;

Loc Code   -   Item No   -   Variant Code   -   Qty
Location1       Item01          Variant01            -1
Location1       Item01          Variant01            -1

SUM and JOIN (the sql query result should be like this)


Loc Code - Item No - Description   -   Description2   -   Variant Code   -Transfer(Qty) - Sales(Qty)  -   Total
Location1       Item01         Active         Black                   Variant01             3                      -2                   1
Location1       Item01         Active         Black                   Variant02             2                       0                   2


Thank you.

here's my updated sql query;

SELECT
[GROUPBY1].[TSL1] AS [LOC CODE]
,[GROUPBY1].[TSL2] AS [ITEM NO]
,[GROUPBY1].[TSL3] AS [DESC]
,[GROUPBY1].[TSL4] AS [DECS2]
,[GROUPBY1].[TSL5] AS [VARIANT CODE]
,[GROUPBY1].[TSL6] AS [QTY]
FROM
(
[Transfer Shipment Line].[Transfer-to Code] AS TSL1
,[Transfer Shipment Line].[Item No_] AS TSL2   (error on this line)
,[Transfer Shipment Line].[Description] AS TSL3
,[Transfer Shipment Line].[Description 2] AS TSL4
,[Transfer Shipment Line].[Variant Code] AS TSL5
,SUM([Transfer Shipment Line].[Quantity]) AS TSL6
FROM
[Transfer Shipment Line] AS [TransferShipmentLine]
INNER JOIN [Trans_ Sales Entry] AS [Trans_ Sales Entry]
ON
[Transfer Shipment Line].[Transfer-to Code] = [Trans_ Sales Entry].[Store No_] AND
[Transfer Shipment Line].[Item No_] = [Trans_ Sales Entry].[Item No_] AND
[Transfer Shipment Line].[Variant Code] = [Trans_ Sales Entry].[Variant Code]
WHERE
[Transfer Shipment Line].[Transfer-to Code]='LOCATION1' AND
[Transfer Shipment Line].[Item No_]='ITEM01' AND
[Transfer Shipment Line].[Variant Code]='VARIANT01'
GROUP BY
[Trans_ Sales Entry].[Store No_]
,[Trans_ Sales Entry].[Item No_]
,[Trans_ Sales Entry].[Variant Code]
) AS [GROUPBY1]

I am getting this error message;

Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ','.

Thank you!