Let us suppose that we have a table:

CREATE TABLE
transactions(
currencyID_1 int,
value_1 money,
currencyID_2 int,
value_2 money
)



I need to calculate the totals by each currency.
It might be express with the construction like this:

SELECT currency, sum(value)
FROM(
SELECT
currencyID_1 AS currency, sum(value_1) AS value
FROM transactions
GROUP BY currencyID_1
UNION ALL
SELECT
currencyID_2 AS currency, sum(value_2) AS value
FROM transactions
GROUP BY currencyID_2
)
GROUP BY currency

But if I'm not wrong it is not able to use subquery in FROM clause.

I have two questions.

1) Why it is prohibited to use subselect in FROM clause?
2) How to solve my task with the most graceful way? (view? temporary
table?)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!