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

glennyboy 121 Reputation points
2020-08-28T07:28:26.103+00:00

Good day!

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

here's the sample data tables;

21147-image.png

here's my sql query code

SELECT	  
MAX([BBI$Transfer Shipment Line].[Transfer-to Code]) AS [Loc Code]  
,MAX([BBI$Transfer Shipment Line].[Item No_])  
,MAX([BBI$Transfer Shipment Line].[Description])  
,MAX([BBI$Transfer Shipment Line].[Description 2])  
,MAX([BBI$Transfer Shipment Line].[Variant Code])  
,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Transfer Order]  
,COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS [Sales Entry]  
,SUM([BBI$Transfer Shipment Line].[Quantity])+COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS Total  
FROM [BBI$Transfer Shipment Line]  
LEFT JOIN [BBI$Trans_ Sales Entry]  
ON [BBI$Transfer Shipment Line].[Transfer-to Code]=[BBI$Trans_ Sales Entry].[Store No_] AND   
[BBI$Transfer Shipment Line].[Item No_]=[BBI$Trans_ Sales Entry].[Item No_] AND  
[BBI$Transfer Shipment Line].[Variant Code]=[BBI$Trans_ Sales Entry].[Variant Code]  
WHERE  
[BBI$Transfer Shipment Line].[Transfer-to Code]='LOCATION1' AND  
[BBI$Transfer Shipment Line].[Item No_]='ITEM01' AND  
[BBI$Transfer Shipment Line].[Variant Code]='VARIANT01'  
GROUP BY  
[BBI$Transfer Shipment Line].[Transfer-to Code]  
,[BBI$Transfer Shipment Line].[Item No_]  
,[BBI$Transfer Shipment Line].[Variant Code]  

but the sql query duplicates result;

21106-image.png

Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-08-28T09:45:51.15+00:00

    HI @glennyboy ,

    Please refer below query:

    SELECT      
     MAX([BBI$Transfer Shipment Line].[Transfer-to Code]) AS [Loc Code]  
     ,MAX([BBI$Transfer Shipment Line].[Item No_]) AS [Item No_]  
     ,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
     ,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description2]  
     ,MAX([BBI$Transfer Shipment Line].[Variant Code]) AS [Variant Code]  
     ,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Transfer(Qty)]  
     ,COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS [Sales(Qty)]  
     ,SUM([BBI$Transfer Shipment Line].[Quantity])+COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS Total  
     FROM [BBI$Transfer Shipment Line]  
     LEFT JOIN (SELECT DISTINCT * FROM [BBI$Trans_ Sales Entry]) [BBI$Trans_ Sales Entry]  
     ON [BBI$Transfer Shipment Line].[Transfer-to Code]=[BBI$Trans_ Sales Entry].[Store No_] AND   
     [BBI$Transfer Shipment Line].[Item No_]=[BBI$Trans_ Sales Entry].[Item No_] AND  
     [BBI$Transfer Shipment Line].[Variant Code]=[BBI$Trans_ Sales Entry].[Variant Code]  
     WHERE  
     [BBI$Transfer Shipment Line].[Transfer-to Code]='LOCATION1' AND  
     [BBI$Transfer Shipment Line].[Item No_]='ITEM01' --AND  
    -- [BBI$Transfer Shipment Line].[Variant Code]='VARIANT01'  
     GROUP BY  
     [BBI$Transfer Shipment Line].[Transfer-to Code]  
     ,[BBI$Transfer Shipment Line].[Item No_]  
     ,[BBI$Transfer Shipment Line].[Variant Code]  
    

    Output:

    21777-result.png

    If the response is helpful, please click "Accept Answer" and upvote it.

    Best regards
    Melissa


  2. Viorel 110.7K Reputation points
    2020-08-28T10:45:39.41+00:00

    Try a query like this example too:

    ;
    with A as
    (
     select [Loc Code], [Item No], [Variant Code], [Qty], 0 as nqty 
     from [BBI$Transfer Shipment Line]
     union all
     select [Loc Code], [Item No], [Variant Code], 0, [Qty]
     from [BBI$Trans_ Sales Entry]
    )
    select [Loc Code], [Item No], [Variant Code], 
     min([Description]) as [Description], 
     min([Description2]) as [Description2],
     sum(Qty) as [Transfer (Qty)], 
     sum(NQty) as [Sales (Qty)], 
     sum(Qty) + sum(NQty) as Total
    from A 
    cross apply ( 
     select top(1) [Description], [Description2] 
     from [BBI$Transfer Shipment Line]
     where [Loc Code] = A.[Loc Code] and [Item No] = A.[Item No] and [Variant Code] = A.[Variant Code] ) as B
    group by [Loc Code], [Item No], [Variant Code]