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

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

Open in new window

solarissfAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
virtuadeptConnect With a Mentor Commented:
You can't refer to the "as XYZ" names you gave other expressions. You have to repeat them to do the math.

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

, ( (
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'
) - (SUM(finalTable.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 

Open in new window

0
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
You cannot refer to column aliases in other references in the query other than the Order by clause.
You can repeat the equation/calculation like stated above or you could extend your query one more time and do that final calculation in an outer query.
SELECT *
, x.[NetLiqVal] - x.[TotalPLasofSettle] AS caclll
FROM (
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


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
) x

Open in new window

0
 
virtuadeptCommented:
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.
0
 
solarissfAuthor Commented:
thanks everyone... oh well, was hoping for something easier.
appreciate the information!
0
All Courses

From novice to tech pro — start learning today.