SQL Subquery Join and Sum

Question

I have Table 1 & 2 with common Column name ID in both.

Table 1 has duplicate entries of rows which I was able to trim using:

SELECT DISTINCT 

Table 2 has duplicate numeric entries(dollarspent) for ID's which I needed and was able to sum up:

Table 1         Table 2 
------------   ------------------
ID     spec    ID       Dol1     Dol2
54      A      54        1         0
54      A      54        2         1
55      B      55        0         2
56      C      55        3         0

-I need to join these two queries into one so I get a resultant JOIN of Table 1 & Table 2 ON column ID, (a) without duplicates in Table 1 & (b) Summed $ values from Table 2

For eg:

NewTable
----------------------------------------
ID     Spec          Dol1           Dol2
54      A             3               1
55      B             3               2

Notes : No. of rows in Table 1 and 2 are not the same.

Thanks

Answer 1

Use a derived table to get the distinct values from table1 and simply join to table 2 and use aggregation.

The issue you have is you have a M:M relationship between table1 and table2. You need it to be a 1:M for the summations to be accurate. Thus we derive t1 from table1 by using a select distinct to give us the unique records in the 1:M relationship (assuming specs are same for each ID)

SELECT T1.ID, T1.Spec, Sum(T2.Dol1) as Dol1, sum(T2.Dol2) as Dol2
FROM (SELECT distinct ID, spec
      FROM table1) T1
INNER JOIN table2 T2
 on t2.ID = T1.ID
GROUP BY T1.ID, T1.Spec

This does assume you only want records that exist in both. Otherwise we may need to use an (LEFT, RIGHT, or FULL) outer join; depending on desired results.

Answer 2

I can't really see your data, but you might want to try:

SELECT DISTINCT ID
FROM TblOne

UNION ALL

SELECT DISTINCT ID, SUM(Dol)
FROM TblTwo
GROUP BY ID

Answer 3

Pre-aggregate table 2 and then join:

select t1.id, t1.spec, t2.dol1, t2.dol2
from (select t2.id, sum(dol1) as dol1, sum(dol2) as dol2
      from table2 t2
      group by t2.id
     ) t2 join
     (select distinct t1.id, t1.spec
      from table1 t1
     ) t1
     on t1.id = t2.id;

For your data examples, you don't need to pre-aggregate table 2. This gives the correct sums -- albeit in multiple rows -- if table1 has multiple specs for a given id.