How to join two tables and do mathematical calculations

Question

I have two tables, EmployeeDetails and CompOffTable. I want to add rows of CompOff table and the result i get on the count of each row should be added to a column to EmployeeDetails table. I have used Left Out Join. Please Help me with this code.

SELECT 
  a.EmpID, 
  a.EmpName, 
  a.Department, 
  a.Designation, 
  a.TotalLeaves, 
COUNT(b.EmpName) AS TotalCompOff                         
FROM (EmployeeDetails a LEFT OUTER JOIN
(CompOffTable b ON a.EmpName = b.EmpName) 
INNER JOIN SUM(a.TotalLeaves + TotalCompOff) AS RemainingLeaves )                        
GROUP BY a.EmpID, a.EmpName, a.Department, a.Designation, a.TotalLeaves

Answer 1

INNER JOIN SUM(a.TotalLeaves + TotalCompOff) AS RemainingLeaves )       

You cannot join to that calculation. Assuming the calculation is what you are seeking, try the following which simply moves it back into the select clause. Also note that you cannot refer to the column alias totalcompoff in the same select clause. You simply have to repeat the method for arriving at the column COUNT(b.empname) instead. This does not make the query any less efficient by the way.

SELECT
      a.empid
    , a.empname
    , a.department
    , a.designation
    , a.totalleaves
    , COUNT(b.empname) AS totalcompoff
    , SUM(a.totalleaves + COUNT(b.empname)) AS remainingleaves
FROM employeedetails a
LEFT OUTER JOIN compofftable b ON a.empname = b.empname
GROUP BY
      a.empid
    , a.empname
    , a.department
    , a.designation
    , a.totalleaves

If the db type/version does not allow that construct then use a derived table approach, and here you can re-use the column alias:

SELECT
      d.empid
    , d.empname
    , d.department
    , d.designation
    , d.totalleaves
    , d.totalcompoff
    , SUM(d.totalleaves + d.totalcompoff) AS remainingleaves
FROM (
      SELECT
            a.empid
          , a.empname
          , a.department
          , a.designation
          , a.totalleaves
          , COUNT(b.empname) AS totalcompoff
      FROM employeedetails a
      LEFT OUTER JOIN compofftable b ON a.empname = b.empname
      GROUP BY
            a.empid
          , a.empname
          , a.department
          , a.designation
          , a.totalleaves
      ) d