Unable to use ALIASES in subquery inside SQL Select clause

Question

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'.

This looks very strange, I think you should provide sample input and expected outcome to make sure you get a correct answer. — Jonas Metzler, Jun 16 '22 at 11:30
Side note: you've tagged [[tag:correlated-subquery]] here, however, the subquery you have is *not* correlated. — Thom A, Jun 16 '22 at 12:24

score 5 · Accepted Answer · answered Jun 16 '22 at 11:32

5

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;

answered Jun 16 '22 at 11:32

Thom A

88,727
11
45
75

Arulkumar · Answer 2 · 2022-06-16T11:36:46.253

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;

Unable to use ALIASES in subquery inside SQL Select clause

2 Answers2