Subquery in FROM clause

[Evgeny Gopengauz]

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!

[Dan Guzman]

This is technically not a subquery but a derived table so you need to
specify an alias:

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
) AS MyDerivedTable
GROUP BY currency

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Evgeny Gopengauz" <ev***@ucs.ru> wrote in message
news:41**********************@news.newsgroups.ws.. .

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!

[Evgeny Gopengauz]

Thank you for your answer!
I'm sorry for the stupid quiestion, I just found the answer in the "FROM
clause" of BOL. But when I read it in the first time I didn't guess that
"derived_table" is the same as subquery and alias is required.

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

[Erland Sommarskog]

Evgeny Gopengauz (ev***@ucs.ru) writes:

Thank you for your answer!
I'm sorry for the stupid quiestion, I just found the answer in the "FROM
clause" of BOL. But when I read it in the first time I didn't guess that
"derived_table" is the same as subquery and alias is required.

Derived table and subqueries are not the same. :-)

A derived table is something you can use where can have a table, that is
in a FROM clause. A subquery can appear in an expression, for instance:

SELECT x, (SELECT MAX(y) FROM a WHERE a.id = b.id)
FROM a
WHERE col = (SELECT MAX(z) FROM c)

Here are two subqueries, of which the first is correlated with the main
query, the other is not.

A derived table is always uncorrelated.

By the way:

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

This is the point where I play Joe Celko and point out that using
integer ids for currencies is a bad thing. Currencies is one of the
few entities that actually has stable useful natural keys, as defined
by ISO 4217. (See for instance http://www.xe.com/iso4217.htm.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp