Subtract one field from another but 1 field is created of subquery

Question

SELECT 
E.`employee_id`,
E.`full_name`,
LE.`no_of_leaves` AS AllocatedLeaves,
MLLT.`leave_type` AS LeaveTypeName,
(SELECT COUNT(*) FROM leave_approval WHERE employee_id = 1 AND MONTH(approval_date) = 11 GROUP BY approval_date) AS TotalLeavesTaken,
LE.`no_of_leaves` - TotalLeavesTaken AS Balance
FROM 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 is what I have tried to do, but got error..

LE.`no_of_leaves` - TotalLeavesTaken AS Balance

I'm not good in databases, I have to subtract 1 field from another, the first field is ok, but the second field is generated as of subquery. I don't want to run subquery again; is it possible some way to do subtraction without using subquery again?

Answer 1

Try this:

SELECT E.employee_id, E.full_name, LE.no_of_leaves AS AllocatedLeaves,
       MLLT.leave_type AS LeaveTypeName,
      (LE.no_of_leaves - SUM(CASE WHEN LA.employee_id = 1 AND MONTH(LA.approval_date) = 11 THEN 1 ELSE 0 END)) AS balance
FROM 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 BY E.employee_id;

EDIT

If you want to count the leave balance each employewise

SELECT E.employee_id, E.full_name, LE.no_of_leaves AS AllocatedLeaves,
       MLLT.leave_type AS LeaveTypeName, LA.TotalLeavesTaken, 
      (LE.no_of_leaves - LA.TotalLeavesTaken) AS balance
FROM 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) As TotalLeavesTaken 
           FROM leave_approval LA 
           WHERE MONTH(LA.approval_date) = 11 
           GROUP BY LA.employee_id
         ) AS 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 BY E.employee_id;