SQL Statment Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Statment Help

Author

Topic

steveb1164
Starting Member

1 Post

Posted - 2003-09-23 : 11:07:46

I have 3 different tables with the same structure. Each row has an ID. Some of the IDs are the same in different tables. I need to add together the data from certain columns of different tables where the IDs are the same, but also include every ID that doesn't match in one of the other tables. For example:

Table 1:
ID, hits, walks
12 2 3
13 4 2
14 4 3

Table 2:
ID, hits, walks
12 3 5
15 2 2
16 1 3

Table 3:
ID, hits, walks
12 2 1
13 4 2
16 2 3

The SQL statement should return this:
ID, hits, walks
12 7 9
13 8 4
14 4 3
15 2 2
16 3 6

I'm going to put this data into an array on my webpage and use the array with ASP and VBscript to calculate other things and form my page. Maybe there is a better way to create this array using code instead of SQL. Can anyone help me out? Thanks.

Steve

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-23 : 11:15:16

Try this:

SELECT	ID, sum(hits) hits, sum(walks) walks
FROM	(
	SELECT	ID, hits, walks
	FROM	Table1

	UNION ALL

	SELECT	ID, hits, walks
	FROM	Table2

	UNION ALL

	SELECT	ID, hits, walks
	FROM	Table 3
	) x
GROUP BY ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources