?
Solved

Nested Select Statements w/ addition

Posted on 2008-11-19
6
Medium Priority
?
486 Views
Last Modified: 2012-05-05
Basically, I want to know if there is a way to run 2 nested select statements in a query and then take the value of those and run a calculation on them without having to re-query them (or duplicate the selects in the statement).  Attached is a simplified example of what I'd like to do, but doesn't work (my real statement is very large which is why I'm looking for a better way to do this).  Obviously, it fails on the line that adds the 2 values together because it can't use those even though I've named them.  I get that, but I'm wondering if there's a good way around it without writing a custom stored procedure or function.
Select a.ACCOUNT_NUMBER,
	(Select Sum(INCOME_CASH_AMOUNT) From TRANSACTIONS Where TRANSACTIONS.ACCOUNT_NUMBER=t.ACCOUNT_NUMBER) As INCOME_AMOUNT,
	(Select Sum(PRINCIPAL_CASH_AMOUNT) From TRANSACTIONS Where TRANSACTIONS.ACCOUNT_NUMBER=t.ACCOUNT_NUMBER) As PRINCIPAL_AMOUNT,
	(Select INCOME_AMOUNT + PRINCIPAL_AMOUNT) As TOTAL_AMOUNT
From ACCOUNT a
Inner Join TRANSACTIONS t On t.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER

Open in new window

0
Comment
Question by:IUFITS
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22997821
what about this:
Select a.ACCOUNT_NUMBER
, Sum(t.INCOME_CASH_AMOUNT) As INCOME_AMOUNT
, Sum(t.PRINCIPAL_CASH_AMOUNT) As PRINCIPAL_AMOUNT
, Sum(t.INCOME_CASH_AMOUNT + t.PRINCIPAL_CASH_AMOUNT)As TOTAL_AMOUNT
From ACCOUNT a
Inner Join TRANSACTIONS t On t.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
group by a.ACCOUNT_NUMBER

Open in new window

0
 
LVL 3

Author Comment

by:IUFITS
ID: 22997859
That would work well in the simplified example I provided.  
My sub-queries are a lot larger with joins and on each that do a lot of calculating, and they're bulky text wise which makes the query difficult to read. I guess what I'm asking is, is there a way to re-use the INCOME_AMOUNT value that I've selected in another calculation (or, even in a where statement for that matter).
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22997901
no, you cannot immediately reuse a column name.

you can do this:
select ACCOUNT_NUMBER
  , INCOME_AMOUNT
  , PRINCIPAL_AMOUNT
  , INCOME_CASH_AMOUNT + PRINCIPAL_CASH_AMOUNT As TOTAL_AMOUNT
FROM (
Select a.ACCOUNT_NUMBER
, Sum(t.INCOME_CASH_AMOUNT) As INCOME_AMOUNT
, Sum(t.PRINCIPAL_CASH_AMOUNT) As PRINCIPAL_AMOUNT
From ACCOUNT a
Inner Join TRANSACTIONS t On t.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
group by a.ACCOUNT_NUMBER
) as subquery

Open in new window

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 3

Author Comment

by:IUFITS
ID: 22997921
Ahhhhhah!   That answers my question and gives me a good route to go on.  I'm going to accept that answer but I have one more question in that is there anything wrong with running the query like that that you can think of off the top of your head?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22997970
nothing wrong AFAIK, especially with "complex" subqueries it will save lots of code and CPU/IO ...
0
 
LVL 3

Author Comment

by:IUFITS
ID: 22997993
Thanks again angellll, it's much appreciated!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question