i am in the " subquery in select Clause " chapter to SQL module
just wanted to confirm or correct my understanding that, the ALIAS used for a subquery can be used in calculation of difference using SELECT and alias name , however we cannot use Alias name denoted to without subquery to a calculation
say
select
client_id, name,
SUM(invoice_total) AS total,
(
select AVG(invoice_total)
from invoices
)
AS AVG
and now trying to find diff between AS total and AS Avg columns
(
SELECT total - AVG
)
AS difference
error -Error Code: 1247. Reference ‘AB’ not supported (reference to group function)
complete code
select
client_id, name,
SUM(invoice_total) AS AB,
(
select AVG(invoice_total)
from invoices
)
AS AV,
(
SELECT AB - AV
)
AS difference
from Clients
left JOIN Invoices USING (Client_id)
GROUP BY client_id