473,899 Members | 4,012 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Subquery in FROM clause

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!
Jul 20 '05 #1
3 24527
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!

Jul 20 '05 #2
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_ta ble" 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!
Jul 20 '05 #3
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_ta ble" 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
13420
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it just won't work. When running columnAlias IS NOT NULL i just get the error "Invalid column name 'columnAlias'. This is the query: SELECT k.UserId, k.Lastname, k.Firstname, (SELECT kscr.answer FROM Results kscr WHERE kscr.UserID =
2
3686
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n group by a_id) as v USING (a_id) where there's an index on b.a_id. assume there are other tables being joined so I can't just move the aggregate to the outermost layer.
2
6404
by: btober | last post by:
I run the following script to export some data from my development database and then update or insert the records into to the quality assurance testing database, but I get a warning notice that I don't understand. Aside from that notice, the script appears to work as intended, i.e., updating existing report definitions and adding any new ones defined in the dev environment but not appearing in QAT. Here is the script: \set...
4
3831
by: Kenny G | last post by:
Below is a query that I currently have. I need to produce a subquery so that the top five of the CodeCount is returned. I appreciate your help. SELECT .PX_SURGEON, .PX_CODE, Count(.PX_CODE) AS CodeCount, .PX_TITLE FROM INNER JOIN ON .PX_CODE = .PX_CODE GROUP BY .PX_SURGEON, .PX_CODE, .PX_TITLE ORDER BY .PX_SURGEON, Count(.PX_CODE)
8
19613
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two fields from a *single* record from a table called 'Locations' and then apply one of these field's values...
5
10162
by: No bother | last post by:
I am using 5.0.26-NT on Windows 2000. I have need to use a reference in the outer from clause in a subquery in the select clause. Consider the following example: Select (select b.baitID from b where b.entrydate curdate()) as wantedBaitIDs from bait_tbl b; My actual need is more complex than this as part of it is a rough cross tab. If I try to define the table in the alias, not only do I lose
1
7164
by: Franc Walter | last post by:
Hello, i didn't find it in the help, i think it is not possible, but i try to question anyway: Is the MySQL command "LIMIT" possible with a subquery in MySQL 4.0.27? e.g.: SELECT * FROM tab1 LIMIT (SELECT COUNT(*) from tab2) this doesn't work. MySQL 5.1 writes: User variables may be used in contexts where expressions are allowed.
5
8041
by: Anne | last post by:
Hello! Here is the statement in question: --STATEMENT A SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTable) The problem with Statement A is that 'colX' does not exist in 'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'. Breaking the statement down, we have:
1
4178
by: jcf378 | last post by:
Hi all-- Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter. Allen Browne...
0
11272
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10863
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10971
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9666
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.