SQL Server Removing Dupes in joins [closed]

vs91vp4v 于 9个月前发布在其他

关注(0)|答案(1)|浏览(81)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 13 days ago.
Improve this question

Nothing I've tried removes dupes from table 1

Here is a basic join of my tables

SELECT t1.ORDERNUM,
    t1.TOTALAMT,
    t2.refrence,
    t2.DEBITAMT
FROM table1 t1
JOIN table2 t2
       ON t1.ORDERNUM = t2.REFRENCE
WHERE t2.REFRENCE = 'PO_309-20142153'

I end up with

ORDERNUM    TOTALAMT    refrence                    DEBITAMT
309-20142153    1420.920000 PO_309-20142153_                0.00000
309-20142153    1420.920000 PO_309-20142153_                62.15000
309-20142153    1420.920000 PO_309-20142153_                86.85000
309-20142153    1420.920000 PO_309-20142153_                129.56000
309-20142153    1420.920000 PO_309-20142153_                201.97000
309-20142153    1420.920000 PO_309-20142153_                376.80000
309-20142153    1420.920000 PO_309-20142153_                563.59000

^^^^^^^^^^^ How do I remove these dupes?

Expected first two records:
| ORDERNUM | TOTALAMT | refrence | DEBITAMT |
| ------------ | ------------ | ------------ | ------------ |
| 309-20142153 | 1420.920000 | PO_309-20142153_ | 0.00000 |
| PO_309-20142153_ | 62.15000 | PO_309-20142153_ | 86.85000 |

I have tried the joins, a subquery, group bys. I either get dupes for table1 or all nulls.

I'm hoping to remove the duplicates in table 1 so I can add it to an SSRS report cleanly. and sum them up without having 2 bazillion in the 2nd column

I am running SQL Server

sql-server

来源：https://stackoverflow.com/questions/77699911/removing-dupes-in-joins

1条答案

按热度按时间

64jmpszr1#

Your fail with group by was due to the fact that you grouped by the last column as well which differs from line to line. Do it like this:

SELECT t1.ORDERNUM,
    t1.TOTALAMT,
    t2.refrence,
    SUM(t2.DEBITAMT) AS DEBITAMT
FROM table1 t1
JOIN table2 t2
       ON t1.ORDERNUM = t2.REFRENCE
WHERE t2.REFRENCE = 'PO_309-20142153'
GROUP BY t.ORDERNUM, t1.TOTALAMT, t2.refrence

赞(0）回复(0）举报 9个月前

我来回答

SQL Server Removing Dupes in joins [closed]

1条答案

相关问题

热门标签

最新问答