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

Hey, @ryan199004. Would you be able to repost this and format it properly? You might consider going to: https://www.tablesgenerator.com/markdown_tables for some help. If you'd like, take a picture and I can try and help. — artemis, Feb 19 '18 at 15:25
The real question here is why do you have duplicates on ID in Table 1 in the first place. Any solution developed is likely addressing a symptom and not root cause. But given the data is appropriate... solutions below. — xQbert, Aug 21 '20 at 18:12

xQbert · Accepted Answer · 2018-02-19T15:33:39.467

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.

score 0 · Answer 2 · answered Feb 19 '18 at 15:28

0

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

answered Feb 19 '18 at 15:28

artemis

6,857
11
46
99

score 0 · Answer 3 · answered Feb 19 '18 at 15:31

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.

SQL Subquery Join and Sum

3 Answers3