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


Trying to SUM and JOIN 2 columns with different tables but it duplicates results, here is my query

SELECT DISTINCT
[Transfer Shipment Line].[Transfer-to Code] AS [Loc Code]
,[Transfer Shipment Line].[Item No_]
,[Transfer Shipment Line].[Description]
,[Transfer Shipment Line].[Description 2]
,[Transfer Shipment Line].[Variant Code]
,[Transfer Shipment Line].[Quantity] AS [QTY]
FROM [Transfer Shipment Line]
LEFT JOIN [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

Query result (correct quantity)

Loc Code   -   Item No_   -   Description   -   Description2   -   Variant Code   -   QTY
LOCATION1   ITEM01         ACTIVE            BLACK                VARIANT01          1
LOCATION1   ITEM01         ACTIVE            BLACK                 VARIANT01         2

***************************************************************************************

SUM QUERY

SELECT DISTINCT
MAX([Transfer Shipment Line].[Transfer-to Code]) AS [Loc Code]
,MAX([Transfer Shipment Line].[Item No_])
,MAX([Transfer Shipment Line].[Description])
,MAX([Transfer Shipment Line].[Description 2])
,MAX([Transfer Shipment Line].[Variant Code])
,SUM([Transfer Shipment Line].[Quantity]) AS [QTY]
FROM [Transfer Shipment Line]
LEFT JOIN [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
[Transfer Shipment Line].[Transfer-to Code]
,[Transfer Shipment Line].[Item No_]
,[Transfer Shipment Line].[Variant Code]

Query result (with SUM)

Loc Code   -   Item No_   -   Description   -   Description2   -   Variant Code   -   QTY
LOCATION1   ITEM01         ACTIVE            BLACK                VARIANT01          9

********************************************************************************************

Asked by:- glennyboy
0
: 133 At:- 8/26/2020 9:09:15 AM
SQL sql query Sum sql join sql

Can you show your demo table data? Edit question and upload a image. 1
By : vikas_jk - at :- 8/26/2020 10:12:22 AM
Both table data without join. 1
By : vikas_jk - at :- 8/26/2020 10:12:49 AM





4 Answers
profileImage Answered by:- vikas_jk

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

Suppose you "TransferShipmentLine" table look like this

table-1-min.png

and second table "TransSalesEntry" looks like this

table-2-min.png

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

output-group-by-join-sum-min.png

1
At:- 8/26/2020 11:24:03 AM
thank you for your reply, please see the sample data table below. 0
By : glennyboy - at :- 8/27/2020 9:36:05 AM


profileImage Answered by:- vikas_jk

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.

output-min.png

1
At:- 8/28/2020 11:34:07 AM


profileImage Answered by:- glennyboy

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.

 

0
At:- 8/27/2020 9:35:24 AM
Please check my above answer, it should help. Also, create relations between two tables, for JOINS. 0
By : vikas_jk - at :- 8/27/2020 2:01:31 PM


profileImage Answered by:- glennyboy

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!

0
At:- 8/28/2020 3:17:02 AM Updated at:- 8/28/2020 3:44:59 AM
Select statement is missing inside GroupBy, also you need to add column "Description" inside inner GroupBy , check second answer with updated query. 0
By : vikas_jk - at :- 8/28/2020 11:36:01 AM
im getting this error Msg 4104, Level 16, State 1, Line 21 The multi-part identifier "BBI$Transfer Shipment Line.Transfer-to Code" could not be bound. Msg 4104, Level 16, State 1, Line 21 The multi-part identifier "BBI$Trans_ Sales Entry.Store No_" could not be bound. Msg 4104, Level 16, State 1, Line 22 The multi-part identifier "BBI$Transfer Shipment Line.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 22 The multi-part identifier "BBI$Trans_ Sales Entry.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 23 The multi-part identifier "BBI$Transfer Shipment Line.Variant Code" could not be bound. Msg 4104, Level 16, State 1, Line 23 The multi-part identifier "BBI$Trans_ Sales Entry.Variant Code" could not be bound. Msg 4104, Level 16, State 1, Line 25 The multi-part identifier "BBI$Transfer Shipment Line.Transfer-to Code" could not be bound. Msg 4104, Level 16, State 1, Line 26 The multi-part identifier "BBI$Transfer Shipment Line.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 27 The multi-part identifier "BBI$Transfer Shipment Line.Variant Code" could not be bound. Msg 4104, Level 16, State 1, Line 29 The multi-part identifier "BBI$Trans_ Sales Entry.Store No_" could not be bound. Msg 4104, Level 16, State 1, Line 30 The multi-part identifier "BBI$Trans_ Sales Entry.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 31 The multi-part identifier "BBI$Trans_ Sales Entry.Variant Code" could not be bound. Msg 4104, Level 16, State 1, Line 11 The multi-part identifier "BBI$Transfer Shipment Line.Transfer-to Code" could not be bound. Msg 4104, Level 16, State 1, Line 12 The multi-part identifier "BBI$Transfer Shipment Line.Item No_" could not be bound. Msg 4104, Level 16, State 1, Line 13 The multi-part identifier "BBI$Transfer Shipment Line.Description" could not be bound. Msg 4104, Level 16, State 1, Line 14 The multi-part identifier "BBI$Transfer Shipment Line.Description 2" could not be bound. Msg 4104, Level 16, State 1, Line 15 The multi-part identifier "BBI$Transfer Shipment Line.Variant Code" could not be bound. Msg 4104, Level 16, State 1, Line 16 The multi-part identifier "BBI$Transfer Shipment Line.Quantity" could not be bound. 0
By : glennyboy - at :- 9/1/2020 7:46:57 AM
Please check my second answer above, when you need to include Column name in inner group by, when you select them in outer group by. if you are new to SQL query, learn more about nested group by. 0
By : vikas_jk - at :- 9/1/2020 9:55:56 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly