How can we Substract SUM(s) of two columns from two tables in Cakephp

Question

I have two tables called SalaryPayment and SalaryStaff.

SalaryStaff Table

+-----------------+------------+-------------+
| SalaryStaffId   | EmployeeId | TotalWage   |    
+-----------------+------------+-------------+
|      6          |    5       |    80       |  
|      7          |    5       |    100      |
|      9          |    5       |    60       | 
+-----------------+------------+-------------+

SalaryPayment table

+-----------------+------------+-------------+
| SalaryPaymentId | EmployeeId | PaidAmount  |    
+-----------------+------------+-------------+
|      1          |    5       |    550      |  
+-----------------+------------+-------------+

I need a result like follows,

+-----------------+-----------------+-----------------+-----------------+
|  EmployeeId     | TotalPaidAmount | TotalWageAmount | PendingAmount   |
+-----------------+-----------------+-----------------+-----------------+
|      5          |    550          |    240          |     210         |
+-----------------+-----------------+-----------------+-----------------+

TotalPaidAmount means SUM(SalaryPayment.PaidAmount )

TotalWageAmount means SUM(SalaryStaff.TotalWage )

PendingAmount means SUM(SalaryStaff.TotalWage ) - SUM(SalaryPayment .PaidAmount )

I've tried the following, it gets TotalPaidAmount gets triples the actual value as 1650 and TotalWageAmount gets proper value.

$this->SalaryStaff->find('all',array(
                'fields'=>array('SalaryPayment.*','SalaryStaff.*','SUM(SalaryStaff.TotalWage) as TotalWageAmount','SUM(SalaryPayment.PaidAmount) as TotalPaidAmount'),
                'joins'=>array(
                    array(                      
                        'alias'=>'SalaryPayment',
                        'table'=>'salary_payments',                     
                        'conditions' =>array('SalaryPayment.EmployeeId = SalaryStaff.EmployeeId')
                        )
                    ),
                'conditions' =>array('SalaryStaff.EmployeeId'=>$EmployeeId),
                'group' => 'SalaryStaff.EmployeeId'
                ));

Answer 1

For your specific problem, you are better off just running two find's to retrieve the two values you need for the that one employee.

To implement a single query solution, it's probably easiest to understand how to write the SQL first, then figure out the Cakephp. Each table's rows needed to be "Grouped" first, then "Join"-ed.

SELECT SalaryStaffGrouped.EmployeeId
     , COALESCE(TotalPaidAmount, 0) AS TotalPaidAmount
     , TotalWageAmount
     , COALESCE(TotalPaidAmount,0)-TotalWageAmount AS PendingAmount
FROM (SELECT EmployeeId, SUM(TotalWage) AS TotalWageAmount
      FROM SalaryStaff
      GROUP BY EmployeeId) AS SalaryStaffGrouped
LEFT JOIN (SELECT EmployeeId, SUM(PaidAmount) AS TotalPaidAmount
      FROM SalaryPayment) AS SalaryPaymentGrouped
  ON SalaryStaffGrouped.EmployeeID = SalaryPaymentGrouped.EmployeeId;

There are a few ways to handle the implementation within Cakephp. I would recommend the query method for its ease. You also could create some MySQL views based on those grouped/ summarized tables, then build Cakephp models based on those views.

---

You need to be aware that this will only list employees found in you SalaryStaff table. If the employee only exists in the SalaryPayment table (and not the SalaryStaff table), the person would not be listed.

---

If you need to filter by EmployeeId, the query will perform better by adding a two WHERE clauses- one to each of the subqueries and not the overall query.

Answer 2

I Think this Query will help :)

SELECT
ss.EmployeeId,
sp.PaidAmount,
SUM(ss.TotalWage) as TotalWageAmount,
sp.PaidAmount - SUM(ss.TotalWage) as PendingAmount
FROM
SalaryStaff ss
left join SalaryPayment sp on (ss.employeeId = sp.employeeid)
group by
ss.employeeid,
sp.PaidAmount