Divide a row by corresponding row in another table

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 2008 Forums

Transact-SQL (2008)

Divide a row by corresponding row in another table

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2012-11-02 : 13:40:55

I have a table, call it A, that has a column of integers.

A.RowID A.Value
1 10
2 20
3 30
4 40
5 50

I have another table, call it B, also with a column of integers.

B.RowID B.Value
3 60
4 70
5 80

I want to divide each B.Value by its corresponding A.Value (a.RowID = b.RowID) without each value of B being divided by only the first value of A, if that makes sense -- which is what I think a subquery in SELECT will do.

dmilam
Posting Yak Master

185 Posts

Posted - 2012-11-02 : 13:59:16

INNER JOINing the two sets and then dividing seems to work, but if there are other solutions, I'd like to see them.


SELECT b.*, a.*, (1.* b.Value / a.Value) as prcnt
FROM 
(
SELECT b.Value, b.ID
FROM B as b
)
INNER JOIN
(
SELECT a.Value, a.ID
FROM A as a
) ON a.ID = b.ID

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-02 : 14:21:36

This is equivalent and simpler:

SELECT b.*, a.*, (1.* b.Value / a.Value) as prcnt
FROM B as b
INNER JOIN A as a ON a.ID = b.ID

dmilam
Posting Yak Master

185 Posts

Posted - 2012-11-02 : 14:48:49

Very nice, always like simpler! Thanks. This will go within some dynamic SQL and incorporate other columns, but it's good to see a foundation to work from.

quote:
Originally posted by robvolk

This is equivalent and simpler:
SELECT b.*, a.*, (1.* b.Value / a.Value) as prcnt
FROM B as b
INNER JOIN A as a ON a.ID = b.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources