Stephen Forero
asked on
sum columns in my select statement - 2008sqlr2
Hi guys,
I have the following select statement that returns a few columns of data. I'm trying to make a new column that subtracts one from another. I am trying the following but am getting the error
Msg 207, Level 16, State 1, Line 22
Invalid column name 'NetLiqVal'.
Can someone tell me how I can perform basic calculations by using the columns of data I just called?
thanks in advance
I have the following select statement that returns a few columns of data. I'm trying to make a new column that subtracts one from another. I am trying the following but am getting the error
Msg 207, Level 16, State 1, Line 22
Invalid column name 'NetLiqVal'.
Can someone tell me how I can perform basic calculations by using the columns of data I just called?
thanks in advance
SELECT finalTable.Account_Number,
(
SELECT dbo.LedgerDB.Journal_Amount
FROM dbo.LedgerDB
WHERE dbo.LedgerDB.Account_ID = finalTable.Account_ID
AND
dbo.LedgerDB.Journal_Type = 'NLV'
AND dbo.LedgerDB.Journal_Date = '20130117'
) AS [NetLiqVal]
,
(
SELECT dbo.LedgerDB.Journal_Amount
FROM dbo.LedgerDB
WHERE dbo.LedgerDB.Account_ID = finalTable.Account_ID
AND
dbo.LedgerDB.Journal_Type = 'MGN'
AND dbo.LedgerDB.Journal_Date = '20130117'
) AS [InitMgn]
,
SUM(finalTable.TotalMarginUsage) AS TotalMarginUsage, SUM(finalTable.TotalPLasofSettle) AS TotalPLasofSettle
, [NetLiqVal] - [TotalPLasofSettle] AS caclll
FROM
(
SELECT newTable.Account_ID, newTable.[Account_Number], newTable.BloombergSymbol, SUM(newTable.[TopdayNet]) AS NetPosition, ABS(SUM(newTable.[TotalMarginUsage])) AS [TotalMarginUsage], SUM(newTable.[P&LasofSettle]) AS TotalPLasofSettle
FROM
(
SELECT dbo.TopdayTradesDB.Account_ID, dbo.TopdayTradesDB.Account_Number, dbo.TopdayTradesDB.BloombergSymbol, SUM(dbo.TopdayTradesDB.Qty_Net) As TopdayNet, SUM(dbo.TopdayTradesDB.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.TopdayTradesDB.[P&L Settlement]) AS [P&LasofSettle]
FROM dbo.TopdayTradesDB
GROUP BY [Account_ID], [Account_Number], BloombergSymbol
UNION ALL
SELECT dbo.DailyPositions_JEFF.Account_ID, dbo.DailyPositions_JEFF.Account_Number, dbo.DailyPositions_JEFF.BloombergSymbol, SUM(dbo.DailyPositions_JEFF.Qty_Net) As TopdayNet, SUM(dbo.DailyPositions_JEFF.[Margin Total USD]) As TotalMarginUsage, SUM(dbo.DailyPositions_JEFF.[P&L Settlement]) AS [P&LasofSettle]
FROM dbo.DailyPositions_JEFF
GROUP BY [Account_ID], [Account_Number], BloombergSymbol
) newTable
GROUP BY newTable.Account_ID, newTable.[Account_Number], newTable.BloombergSymbol
) finalTable
INNER JOIN dbo.AccountsDB
ON dbo.AccountsDB.Account_Number = finalTable.Account_Number
WHERE Client_ID = '418BB7B8-0180-4A5A-99F6-CE52E60E694F'
GROUP BY finalTable.Account_ID, finalTable.Account_Number
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One more thing if you get a "subselect returns more than one value" error then in your sub-selects try using a SUM() (for numerics) or MAX() (for char) so that you do not get multple rows on your sub selects, or be more restrictive in the WHERE on the subselect.
ASKER
thanks everyone... oh well, was hoping for something easier.
appreciate the information!
appreciate the information!