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'
                ));

I think in your example you mean for the TotalPaidAmount to be 550 and the the TotalWageAmount to be 240, based on the data in your tables. — davidethell, Jun 15 '15 at 09:52
Your SalaryPayment amount is being tripled because it sees three records in the SalaryStaff table that it joins against. — davidethell, Jun 15 '15 at 10:00
Since you're in PHP I wouldn't worry about trying to gather all the data in a single query and instead create two queries, one to get the TotalPaidAmount and a second to get the TotalWageAmount. — davidethell, Jun 15 '15 at 11:13

score 1 · Answer 1 · answered Jun 15 '15 at 13:19

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.

score 1 · Answer 2 · answered Jun 15 '15 at 13:45

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

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

2 Answers2