sql - MYSQL | Subtract one field from another but 1 field is created of subquery -

select  e.`employee_id`, e.`full_name`, le.`no_of_leaves` allocatedleaves, mllt.`leave_type` leavetypename, (select count(*) leave_approval employee_id = 1 , month(approval_date) = 11 group approval_date) totalleavestaken, le.`no_of_leaves` - totalleavestaken balance employee e inner join leave_entitlement le on e.`employee_id` = le.`employee_id` inner join `ml_leave_type` mllt on mllt.`ml_leave_type_id` = le.`ml_leave_type_id` left join leave_approval la on e.`employee_id` = la.`employee_id` left join leave_application lapp on lapp.`application_id` = la.`leave_application_id` left join ml_leave_type mlltla on mlltla.`ml_leave_type_id` = lapp.`ml_leave_type_id`  error code: 1054 unknown column 'totalleavestaken' in 'field list' 

this have tried do, got error..

le.`no_of_leaves` - totalleavestaken balance 

im not in databases, have subtract 1 field another, first field ok, second field generated of subquery. dont want run subquery again, possible way subtraction without using subquery again??

try this:

select e.employee_id, e.full_name, le.no_of_leaves allocatedleaves,        mllt.leave_type leavetypename,       (le.no_of_leaves - sum(case when la.employee_id = 1 , month(la.approval_date) = 11 1 else 0 end)) balance employee e inner join leave_entitlement le on e.employee_id = le.employee_id inner join `ml_leave_type` mllt on mllt.ml_leave_type_id = le.ml_leave_type_id left join leave_approval la on e.employee_id = la.employee_id left join leave_application lapp on lapp.application_id = la.leave_application_id left join ml_leave_type mlltla on mlltla.ml_leave_type_id = lapp.ml_leave_type_id group e.employee_id; 

edit

if want count leave balance each employewise

select e.employee_id, e.full_name, le.no_of_leaves allocatedleaves,        mllt.leave_type leavetypename, la.totalleavestaken,        (le.no_of_leaves - la.totalleavestaken) balance employee e inner join leave_entitlement le on e.employee_id = le.employee_id inner join ml_leave_type mllt on mllt.ml_leave_type_id = le.ml_leave_type_id left join (select la.employee_id, count(1) totalleavestaken             leave_approval la             month(la.approval_date) = 11             group la.employee_id          ) la on e.employee_id = la.employee_id left join leave_application lapp on lapp.application_id = la.leave_application_id left join ml_leave_type mlltla on mlltla.ml_leave_type_id = lapp.ml_leave_type_id group e.employee_id;