Unable to use ALIASES in subquery inside SQL Select clause-CodePudding

I'm trying to use an alias inside a subquery in a select statement like the one below:

SELECT 
     InvoiceId,
     InvoiceTotal,
     (SELECT 
       AVG(InvoiceTotal)
       FROM Invoices) AS InvoiceAverage,
     InvoiceTotal - (SELECT InvoiceAverage)
FROM Invoices;

When I try to execute the query I get this error:

'Invalid column name 'InvoiceAverage'.

CodePudding user response：

No need for a subquery here, use windowed aggregates:

SELECT InvoiceId,
       InvoiceTotal,
       AVG(InvoiceTotal) OVER () AS InvoiceAverage,
       InvoiceTotal - AVG(InvoiceTotal) OVER () AS YourAliasGoesHere
FROM dbo.Invoices;

CodePudding user response：

You may directly minus the sub query value from InvoiceTotal as

SELECT 
     InvoiceId,
     InvoiceTotal,
     (SELECT AVG(InvoiceTotal) FROM Invoices) AS InvoiceAverage,
     (InvoiceTotal - (SELECT AVG(InvoiceTotal) FROM Invoices)) AS InvoiceCalculatedValue
FROM Invoices;

or you may store the AVG value in a variable and use in the select query

DECLARE @InvoiceAverage AS DECIMAL (18,2) = 0;
SELECT @InvoiceAverage = AVG(InvoiceTotal) FROM Invoices;

SELECT 
     InvoiceId,
     InvoiceTotal,
     @InvoiceAverage AS InvoiceAverage
     (InvoiceTotal - @InvoiceAverage) AS InvoiceCalculatedValue
FROM Invoices;