How To Combine Records From Different Tables?

There are two worksheets in same workbook that have the same structure-same field names. for example : Table 1 - Officer name mkt - s15 peter 15 - s17 mary 18 -

Solution 1:

You can use a common table expression to union the tables into one and then perform the aggregate sum. I'm using SET NOCOUNT ON; because I had issues before in excel if I omitted this. A full outer join between the two tables would also work.

SET NOCOUNT ON;

WITH CTE AS
(
    SELECT*FROM [$table1]
    UNIONALLSELECT*FROM [$table2]
)

SELECT office, name, sum(mkt)
FROM CTE
GROUPBY office, name

You can also try without the CTE:

SELECT office, name, sum(mkt)
FROM(
    SELECT*FROM [$table1]
    UNIONALLSELECT*FROM [$table2]
)
GROUPBY office, name

Solution 2:

select officer ,name ,sum(mkt) from table1
    unionallselect officer ,name ,sum(mkt) from table2