DaytonaGuy DaytonaGuy - 1 year ago 71
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?



final result should look like

123 85
145 44

Answer Source

Do the distinct before the join:

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download