DaytonaGuy DaytonaGuy - 2 months ago 9
SQL Question

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

Answer

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.

Comments