SEO

#1 (**permalink**) 02-29-2008, 04:10 AM

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!

#2 (**permalink**) 02-29-2008, 04:10 AM

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" <evgop@ucs.ru> wrote in message
news:413da7de$0$26130$c397aba@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!

#3 (**permalink**) 02-29-2008, 04:10 AM

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!

#4 (**permalink**) 02-29-2008, 04:10 AM

Evgeny Gopengauz (evgop@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, esquel@sommarskog.se

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Subquery in FROM clause