Remove duplicated results using inner join?-CodePudding

I want to see TotalSale values ONLY on the first row of TotalSale that matches with PricesTable DocNumber and DocType

SalesTable
ProductID  DocType  DocNumber  UnitPrice 
01         A        000001        150.00
05         A        000001        200.00
06         A        000001         80.00
65         C        002550      15000.00
30         B        002551        100.00

and

PricesTable
DocType  DocNumber  TotalSale
A        000001       430.00
C        002550     15000.00
B        002551       100.00

What I want to get is something like this

TotalSalesTable
ProductID  DocType  DocNumber  UnitPrice  TotalSale
01         A        000001        150.00     430.00
05         A        000001        200.00
06         A        000001         80.00
65         C        002550      15000.00   15000.00
30         B        002551        100.00     100.00

but instead I keep getting this:

TotalSalesTable
ProductID  DocType  DocNumber  UnitPrice  TotalSale
01         A        000001        150.00     430.00--> Good Value
05         A        000001        200.00     430.00--> This
06         A        000001         80.00     430.00--> and this are duplicated
65         C        002550      15000.00   15000.00
30         B        002551        100.00     100.00

This is the query I'm using:

SELECT ST.ProductID
    ,ST.DocType
    ,ST.DocNumber
    ,ST.UnitPrice
    ,PT.TotalSale
from SalesTable ST
INNER JOIN PricesTable PT
    on (ST.DocNumber=PT.DocNumber)

CodePudding user response：

I think you can try enumerating all the rows, then assign totalsales value only to those rows that are first for every docnumber:

 SELECT ST.ProductID
    ,ST.DocType
    ,ST.DocNumber
    ,ST.UnitPrice
    ,case when row_number() over 
    (partition by st.docnumber order by st.productid) = 1 
         then PT.TotalSale end as TotalSale
from SalesTable ST
INNER JOIN PricesTable PT
    on (ST.DocNumber=PT.DocNumber)

Edit: I would also recommend having two columns: one with a duplicated values for every docnumber, and another one with those values hidden except the first one. So that in future you can calculate ratios and aggregates from the output more comfortably.

CodePudding user response：

You can get the required results without the need of the second table (PricesTable),

Select ProductID, DocType, DocNumber, UnitPrice,
       Case When ROW_NUMBER() Over (Partition By DocType, DocNumber Order By ProductID) = 1
            Then SUM(UnitPrice) Over (Partition By DocType, DocNumber) 
            Else ''
       End as TotalSale
From SalesTable

See a demo from db<>fiddle.