CODEWITHSUNDEEP
sqlt-sql
DaytonaGuy
DaytonaGuy

Reputation: 23

SQL Distinct join with SUM

im facing a little issue with sql join

I have 2 tables

*1st table*                  *2nd table*
Reference   Name             Reference   Amount
123         Test1            123         52
145         Test2            232         22
123         Test3            123         33
555         Test4            145         44
123         Test5            656         55

I need something like

select distinct(a.reference), sum(b.amount) from 1st a left join 2nd b ON a.Reference = b.Reference group by a.reference

this look pretty simple for me, but what it does - it gives me SUM multiple times where reference is 123 so it will give me (52+33)*3

what should I use to get unique SUM's per Reference?

Thx!

edit:

final result should look like

123   85
145   44

Upvotes: 1

Views: 271

Answers (3)

VDK
VDK

Reputation: 583

You can also use a CTE to resolve your issue:

WITH cte
AS
(

SELECT DISTINCT reference FROM 1st 

)

SELECT a.reference, SUM(b.amount)
FROM cte a
LEFT JOIN 2nd b ON a.Reference = b.Reference
GROUP BY a.reference

Upvotes: 0

K.K
K.K

Reputation: 366

Your query is incorrect,

you should not use distinct since we have group by..

So your should be likely,

select a.reference, sum(b.amount) from 1st a 
 left join 2nd b ON a.Reference = b.Reference group by a.reference

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Do the distinct before the join:

select a.reference, sum(b.amount)
from (select distinct reference from a) a left join 
     b 
     a.Reference = b.Reference
group by a.reference;

You almost never need to use select distinct with group by.

Upvotes: 1

Related Questions