Combine/Calculate two Tables

Transact-SQL
1

Hi All,

I have two SQL tables: Table1 and Table2

Please, see below:

table 1 table2

userid month year parts quant list misc others userid month year parts quant list misc others
P5 3 2012 8123 10230 32 183 1200 P5 3 2012 361 102 75 42 0

p6 3 2012 92 132 7 0 75 P6 3 2012 14 2 27 11 0

                                                                                                                        P7             3             2012       5        123        45   788         0

                                                                                                                        P8             3              2012     24        18          6     425        0

                                                                                                                         P9            3              2012      8         74         15     133        0

P10 3 2012 159 456 25 32 100

p11 3 2012 7330 13342 152 138 12350

p18 3 2012 12 42 152 138 1000 P18 3 2012 5 3 8 12 0

P21 3 2012 2041 3835 273 47 198 P21 3 2012 10 20 4 5 0

p23 3 2012 37 12 8 0 27 P23 3 2012 4 7 17 18 0

p24 3 2012 22 47 7 0 0 P24 3 2012 12 6 11 0 0

I am trying to create the final data report into the table 3 from both tables (Table1 and Table2) with SQL Query statement:

SELECT userid, parts, quant, list, misc, others, (parts + quant + list + misc + others) From table1 union all SELECT userid, parts, quant, list, misc, others, (parts + quant + list + misc + others) FROM table2 as TotalResutlsTable3 WHERE month = 3 and year = 2012 and userid like 'P%' order by userid ASC

I am not sure if this SQ query is correct for calculating both table1 and table2 to final data report table3.

Like this final table 3:

Userid month year parts quant list misc others

P5 3 2012 8484 10332 108 225 1200

p6 3 2012 106 134 34 11 75

P7 3 2012 5 123 45 788 0

P8 3 2012 24 18 6 425 0

P9 3 2012 8 74 15 133 0

P10 3 2012 159 456 25 32 100

p11 3 2012 7330 13342 152 138 12350

p18 3 2012 17 45 160 150 1000

P21 3 2012 2051 3855 277 52 198

p23 3 2012 41 19 25 18 27

P24 3 2012 34 52 18 0 0

I am not sure if this SQ query is correct for calculating both table1 and table2 to final data report table3.

Your help is greatly appreciated. Thanks.

2

If your goal is to apply the same filter conditions (WHERE clause) to both tables, then you should put the where clause in each part of the UNION ALL, like shown below. I didn't quite understand the requirement, so I don't know if this will be all that you will need to do, or whether there is more to it.

SELECT  userid ,
        parts ,
        quant ,
        list ,
        misc ,
        others ,
        ( parts + quant + list + misc + others ) AS SomeColName
FROM    table1
WHERE   month = 3
        AND year = 2012
        AND userid LIKE 'P%'
UNION ALL
SELECT  userid ,
        parts ,
        quant ,
        list ,
        misc ,
        others ,
        ( parts + quant + list + misc + others )
FROM    table2 AS TotalResutlsTable3
WHERE   month = 3
        AND year = 2012
        AND userid LIKE 'P%'
ORDER BY userid ASC
1 Like
3

JamesK,

Thanks for your quick response.
After run you SQL query statement, didn't work, it didn't calculate each column from both table into table final results

table1 table2 Final results
userid parts quant list misc userid parts quant list misc userid parts quant list misc
P5 5 12 10 120 P5 10 8 3 15 P5 15 20 13 135

Thanks.

4

If you want matching rows from the two tables to be output to results as a single row, UNION will not do that. You can do something like the following.

You will have to change the join condition based on exactly which columns are to be used for matching up the rows. If there is a row in one table that matches the year, month, userid and the join conditions that you have, but there is no corresponding row in the other table, what do you want to get? The inner join I used below will give you results only if there are matching rows in both tables.

SELECT  a.userid ,
        a.parts ,
        a.quant ,
        a.list ,
        a.misc ,
        a.others ,
        ( a.parts + a.quant + a.list + a.misc + a.others ) AS SomeColNameA,
        b.userid ,
        b.parts ,
        b.quant ,
        b.list ,
        b.misc ,
        b.others ,
        ( b.parts + a.quant + a.list + b.misc + b.others ) AS SomeColNameB
FROM    table1 a
		INNER JOIN table2 b ON
			a.userId = b.UserId
			AND a.parts = b.parts -- this is only an example.  You will have to figure out
			-- what constitutes a match between a row in table A and a row in table B.
			-- Also, if you have multiple rows in one table that matches a single row
			-- in the other table, this would produce dups for the rows in the first table.
WHERE
		a.MONTH = 3 AND b.MONTH = 3
		AND a.YEAR = 2012 AND b.YEAR = 2012
		AND a.userid LIKE 'P%' AND b.userid LIKE 'P%'
1 Like
5

Still not working. It's not calculating each column. Your SQL queries statements are pretty good, but I would like to see as you can see below:

in table 1 ..................... | in table 2 ................... into table 3

col 1 | col2 | col3 ......... col 1 | col2 | col3.............. col 1 | col2 | col3
5 ..... | 2 ..... | 16 ........... 10... | 25....| 3.................. 15... | 27... | 19

6

Add the pairs of columns in the query and insert into your destination table - for example:

INSERT INTO table3
(col1, col2, col3)
SELECT
	a.col1 + b.col1,
	a.col2 + b.col2,
	a.col3 + b.col3
FROM
	Table1 AS a
	INNER JOIN Table2 AS b
		ON 
		--........
1 Like
7

I try it and is give me zero results. Soon I need to give a report of these results, I am kind a little disappointed: I tried but without results.

Is there a way simple for calculating each column as I mentioned above.

8

You can try the following query:

SELECT userid, month, year, sum(parts), sum(quant), sum(list), sum(misc), sum(others) from
(select userid, month, year, parts, quant, list, misc, others from table1 where month = '3' and year = '2012'
and userid like 'P%'
union all
select userid, month, year, parts, quant, list, misc, others from table2 where month = '3' and year = '2012'
and userid like 'P%'
) as tmp
group by userid, month, year

Thanks and Regards
Sina K V

1 Like
9

Hi

Please find one way of doing this

SELECT   sum(a.col1) as sumCol1
	,sum(a.col2)   as sumCol2
	,sum(a.col3)   as sumCol3
FROM (
	SELECT *
	FROM #table1
	
	UNION ALL 
	
	SELECT *
	FROM #table2
	) a

-- Here is the sample data script
/*

IF OBJECT_ID('tempdb..#table1 ') IS NOT NULL
DROP TABLE #table1

CREATE TABLE #table1 (
col1 INT NULL
,col2 INT NULL
,col3 INT NULL
)

INSERT INTO #table1
SELECT 5
,2
,16

IF OBJECT_ID('tempdb..#table2 ') IS NOT NULL
DROP TABLE #table2

CREATE TABLE #table2 (
col1 INT NULL
,col2 INT NULL
,col3 INT NULL
)

INSERT INTO #table2
SELECT 10
,25
,3

*/

1 Like
10

This is SQL Query is working goo, but after run the sql query I would like the final results stored into new physical table named table 5 instead as tmp.

Thanks.

11

Try this.

select * into table5 from (
SELECT userid, month, year, sum(parts), sum(quant), sum(list), sum(misc), sum(others) from
(select userid, month, year, parts, quant, list, misc, others from table1 where month = '3' and year = '2012'
and userid like 'P%'
union all
select userid, month, year, parts, quant, list, misc, others from table2 where month = '3' and year = '2012'
and userid like 'P%'
) as tmp
group by userid, month, year) as a

1 Like
12

First of all, I would like to thank you for the queries and after update a little bit the SQL Query, it worked well.
Again thank you, sinakv so much for your help, also I would like to thank much to JamesK, hhrr... I saved your SQL queries for my future projects.

13

If you like an answer please also click the heart icon.