MySQL - How to use the columns alias from subqueries for another sub query?

Question

select name, 
 (select sum(balance) from customers group by name having balance>0 and `type of contract`!="loan") as holdings, 
 (select sum(balance) from customers group by name having balance<0 or `type of contract`="loan") as borrowings, 
 (select case when holdings-borrowings>0 then "positive" else "negative" end from customers) as `positive/negative`, 
 holdings-borrowings as total 
 from customers 
 group by name 
 order by name;

Error Code: 1054. Unknown column 'holdings' in 'field list'.

The table definition is, name varchar, type of contract varchar, balance int. I know where the error is, I can't use the column alias from the subqueries but I don't know how to execute the query in another method.

Answer 1

You can try the below using conditional aggregation

Note: column alias can not be used as a reference in the projection list that's the reason you got the error

select name, 
sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end) as holdings,
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end) as borrowings,
case when (sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end)-
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end))>0 then 'positive' else 'negative' end as `positive/negative`,
sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end)-
sum(case when balance<0 and `type of contract`="loan" then balance else 0 end) as total
from customers   
group by name 
order by name

OR -

select name, holdings,borrowings,case when holdings-borrowings>0 then 'Postivie' else 'Negative' end as `positive/negative`,holdings-borrowings as total
from
(
select name, 
    sum(case when balance>0 and `type of contract`!="loan" then balance else 0 end) as holdings,
    sum(case when balance<0 and `type of contract`="loan" then balance else 0 end) as borrowings
    from customers   
    group by name
)A order by name