SQL Join two tables with same column names, and get sum of each column

Question

I know that in the long run... the column names being the same is probably irrelevant. But here's what I've got:

Master Table:

| PersonID | 1970 | 1971 | 1972 | 1973 |
|----------|------|------|------|------|
|        1 |   50 |   50 |  100 |   50 |
|        2 |   30 |   30 |   40 |   40 |
|        3 |   40 |   40 |   40 |   20 |

Supplemental Table

| PersonID |   1972 |   1973 |
|----------|--------|--------|
|        1 |    100 | (null) |
|        2 | (null) | (null) |
|        3 | (null) |    200 |

I'd like to have a table (a view, actually) that will join these two tables, and add the sum of the columns into a column with the same name

Combined Table:

| PersonID | 1970 | 1971 | 1972 | 1973 |
|----------|------|------|------|------|
|        1 |   50 |   50 |  200 |   50 |
|        2 |   30 |   30 |   40 |   40 |
|        3 |   40 |   40 |   40 |  220 |

The resulting table needs to have both tables combined.. The years go all the way to 2017, and are the same name for each table.

SQLfiddle

Gordon Linoff · Accepted Answer · 2017-07-07 22:01:29Z

2

If there is only one matching row per id, then left join does it:

select t1.person_id, t1.[1970], t1.[1971],
       (t1.[1972] + coalesce(t2.[1972], 0)) as [1972],
       (t1.[1973] + coalesce(t2.[1973], 0)) as [1973]       
from t1 left join
     t2
     on t1.person_id = t2.person_id

answered Jul 7, 2017 at 22:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Hill · Accepted Answer · 2017-07-07 22:16:00Z

Unless you choose to use dynamic SQL (generally best to avoid to prevent SQL injection, bugs, and negative performance impacts), the column names being equivalent doesn't give us any shortcuts here.

The canonical way to do this would be to sum each column explicitly:

SELECT mt.PersonID, COALESCE(mt.[1970], 0) + COALESCE(st.[1970], 0), COALESCE(mt.[1971], 0) + COALESCE(st.[1971], 0), ... COALESCE(mt.[2017], 0) + COALESCE(st.[2017], 0)
FROM MasterTable mt
LEFT JOIN SupplementalTable st
    ON mt.PersonID = st.PersonID;

However, this sucks with 40 columns (and it sounds like possibly more in the future). Dynamic SQL is generally best to avoid, but sometimes it's still the best tool for the job. Here's how you could solve the same problem with dynamic SQL:

DECLARE @dynamicSql NVARCHAR(MAX);
DECLARE @selectList NVARCHAR(MAX);

SELECT @selectList = COALESCE(@selectList + ', ', '') + 'COALESCE(mt.[' + mtColumns.COLUMN_NAME + '], 0)' + COALESCE(' + COALESCE(st.[' + stColumns.COLUMN_NAME + '], 0)', '')
FROM INFORMATION_SCHEMA.COLUMNS mtColumns
LEFT JOIN INFORMATION_SCHEMA.COLUMNS stColumns ON mtColumns.COLUMN_NAME = stColumns.COLUMN_NAME
WHERE mtColumns.TABLE_NAME = 'MasterTable'
      AND stColumns.TABLE_NAME = 'SupplementaryTable';

SET @dynamicSql = 'SELECT ' + @selectList + ' FROM MasterTable mt INNER JOIN SupplementaryTable st ON mt.PersonID = st.PersonID;';

EXECUTE sp_executesql @dynamicSql;

I haven't generated the actual tables to test this, but it should be fairly close. The query should generate a list of SELECT field sums for each field and then run it. It should also skip non-existent fields in SupplementaryTable, and it will only operate for fields that do exist in MasterTable.

Using an ORM (like Hibernate) can do a great deal towards mitigating potential bugs or SQL injection vulnerabilities by validating column names ahead of time.

I REALLY wanted the dynamic SQL to work, because you are right, it does suck. Unfortunately, when I plugged in my values, I kept getting "Must declare scalar variable @selectList".. I've never ever toyed with dynamic SQL, but I can see plain as day, we are declaring it.

Collectives™ on Stack Overflow

SQL Join two tables with same column names, and get sum of each column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related