COMPUTE in SQL - 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

SQL Server Development (2000)

COMPUTE in SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-20 : 09:18:29

Avi writes "Thanks in Advance.

I have a SQL Statement with a UNION operator. it gets data from two different table (different column names but SAME Data type) and groups data by a key field. here is an example of the result set.

Table 1:
Field X Field Y
   A    10
   B    20
   C    30

Table 2:
Field V Field W
   D    10
   E    20
   C    30
the union of these two is
A 10
B 20
C 30
D 10
E 20
C 30

I want the result set to display
A 10
B 20
C 60 (***)
D 10
E 20

How do i use COMPUTE Operator in this case. (field names are different in two tables)

Thanks.
Avi"

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-02-20 : 09:44:58

There is probably a better way to do this than the following, but here it goes. (For some reason I cannot put a addition operator in the response, so please replace the word plus with the addition operator)

Select a.FieldX, (a.FieldY plus b.FieldW) New_Field
FROM Table1 LEFT OUTER JOIN Table2 ON a.FieldX = FieldV

UNION ALL
Select FieldY, FieldW as New_Field
FROM Table2
WHERE FieldY NOT IN
(Select a.FieldX
FROM Table1 LEFT OUTER JOIN Table2 ON a.FieldX = FieldV)

Again, this is probably not the most efficient way to do this and I am sure someone else will probably have a better solution, but I think the above will work.

Let me know if this doesn't solve the problem.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-20 : 11:04:56

I thought I had that problem with the + in a previous post, but it turns out to be just the preview that doesn't work.

I'm slightly confused by the question, though. What's wrong with doing it this way:


SELECT x, SUM(y)
FROM (
    SELECT x, y FROM t1
    UNION ALL
    SELECT v, w FROM t2) a
GROUP BY x

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-02-20 : 11:37:41

Thanks for cleaning up my SQL Code. I now get to spend a few hours going back to change SP because I had been doing it my way or generating Temp tables to hold data, just because I couldn't figure out how to do a UNION in a sub query. If I ever make it to the UK (or you make it to Oklahoma City, OK), I owe you a beer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources