how to do math on nested queries?

Question

Post reply

how to do math on nested queries?

Gregory Tran

SSC Veteran

Points: 200
More actions
August 7, 2007 at 8:46 am

#174771

i tried googling for this but couldn't find it, and not too sure if i'm searching correctly
i have 2 tables (tblTraderPnL,tblTraderFees)
what i want to do is take the sum of the PnL, and the sum of the fees, to come up with one number - the sum of the 2 sums
when i do them separately, it works fine -
SELECT Sum(tblTraderPnL.[Net P&L]) AS [SumOfNet P&L] FROM tblTraderPnL;
SELECT Sum(tblTraderFees.Fee) AS SumOfFee FROM tblTraderFees;
but that's 2 dif queries, and i want to do this all in one
when i try
select ((Sum(tblTraderPnL.[Net P&L]) as sumpnl
FROM tblTraderPnL) + (SELECT Sum(tblTraderFees.Fee) as sumfee
FROM tblTraderFees))from tblTraderPnL, tblTraderFees
it doesn't work at all... and i can't SUM the two either... how do i do this?
thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PRADEEP K. SSC-Addicted Points: 491 More actions · Answer 1

Hi

What you actually want to do is something like "Select 10+20".

But the reason your last query fails is because you are trying something like "Select 10 + Select 20", which does not work.

Consider using variables as follows:

declare

@sum1 numeric, @sum2 numeric

select @sum1 = sum(tblTraderPnL.[Net P&L]) from tblTraderPnL

select @sum2 = sum(tblTraderFees.Fee) from tblTraderFees

select @sum1 + @sum2

go

Only the last select statement will produce an output. Hope this helps.

Cheers!

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 2

Are you just wanting the total of tblTraderPnL.[Net P&L] and the total of tblTraderFees.Fee? But you want them in one record as the output of a query? If so, then you will need two queries, like these:

SELECT tblTraderPnL.[Net P&L] AS NetPL, 0 AS Fee FROM tblTraderPnL;

UNION

SELECT 0 AS NetPL, tblTraderFees.Fee AS Fee FROM tblTraderFees;

Name this query "qryTraderQuery". Then the second query would look like this

SELECT Sum(NetPL) AS SumOfNetPL, Sum(Fee) AS SubOfFee FROM qryTraderQuery;

Of course you can name the first query whatever you want to, but what ever name you chose, you will need to use that same name within the second query.

If this is not what you are looking for, please give another shot at your explaination.

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

sanjay-439180 Valued Member Points: 64 More actions · Answer 3

Try this:

select sum(col1) as sum from

(select [Net P&L] as col1 from tblTraderPnL union select Fee as col1 from tblTraderFees) as dertab

regards

Sanjay, India

Gregory Tran SSC Veteran Points: 200 More actions · Answer 4

i tried this, but it doesn't work at all... says i have to start with "select" etc...

does this kinda of sql work in access?

Gregory Tran SSC Veteran Points: 200 More actions · Answer 5

this works great TY!

however for some reason when i tried the query as posted, the numbers were off. (couldn't figure out why)

but when i changed it slightly to

select sum(col1) as sum from(select sum([Net P&L]) as col1 from tblTraderPnL union select sum(Fee) as col1 from tblTraderFees)

it worked like a charm... TY for all the help!