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
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.