我的查询正在工作,但我需要计算从查询中创建的列的总数,我使用普通的php脚本来添加累计工作时间的值(以货币计),这也非常有效。但是,我该如何获得这一列的总和或总和,它应该给我一个单一的数字,在回音行中用作"……该期间的累计工资是_",请参阅我的脚本的最后一行。
以下是脚本剪辑:
<?php
include("../xxx");
$cxn = mysqli_connect($host,$user,$password,$dbname)
or die ("Couldn't connect to server.");
$query = "SELECT
ea.`employee_id`,
e.`employee_surname`,
e.`employee_first_name`,
e.`employee_second_name`,
e.`employee_salary`,
FORMAT((IF((SUM(ea.`empl_attendance_total`))<180,(SUM(ea.`empl_attendance_total`)),180)),1) AS nt,
FORMAT((IF(((SUM(ea.`empl_attendance_total`))-(SUM(CASE WHEN WEEKDAY(ea.empl_attendance_date) > 5 THEN ea.empl_attendance_total END)))<=180,
0,(IF(((SUM(ea.`empl_attendance_total`))-(SUM(CASE WHEN WEEKDAY(ea.empl_attendance_date) > 5 THEN ea.empl_attendance_total END)))>180,
((SUM(ea.`empl_attendance_total`))-(SUM(CASE WHEN WEEKDAY(ea.empl_attendance_date) > 5 THEN ea.empl_attendance_total END)))-180,
0)))),1) AS ot,
FORMAT((IF((SUM(ea.`empl_attendance_total`))>180,
IF((SUM(ea.`empl_attendance_total`))-180>=(SUM(CASE WHEN WEEKDAY(ea.empl_attendance_date) > 5 THEN ea.empl_attendance_total END)),
(SUM(CASE WHEN WEEKDAY(ea.empl_attendance_date) > 5 THEN ea.empl_attendance_total END)),(SUM(ea.`empl_attendance_total`))-180),
0)),1) AS st,
FORMAT((SUM(ea.`empl_attendance_total`)),1) AS total
FROM
empl_attendance ea
JOIN
employee e
ON ea.`employee_id` = e.`employee_id`
WHERE ea.`empl_attendance_date` BETWEEN '$start_date' AND '$end_date'
GROUP BY `employee_id`";
$result = mysqli_query($cxn,$query)
or die ("Couldn't execute query.");
$total_salary = 0;
/* Displays items already in table */
echo "<table><br>
<tr>
<th>Empl No</th>
<th>Empl Name</th>
<th>N/T (1.0)</th>
<th>O/T (1.5)</th>
<th>S/T (2.0)</th>
<th>Total Hrs</th>
<th>Est Salary</th>
</tr>";
while($row = mysqli_fetch_assoc($result))
{
extract($row);
$sal = ((($employee_salary/180)*$nt)+((($employee_salary/180)*$ot)*1.5)+((($employee_salary/180)*$st)*2));
$salary = number_format($sal, 2, '.', ',');
// add this salary to the total
$total_salary += $sal;
echo "<tr>'n
<td>$employee_id</td>'n
<td>$employee_surname, $employee_first_name $employee_second_name</td>'n
<td>$nt</td>'n
<td>$ot</td>'n
<td>$st</td>'n
<td>$total</td>'n
<td>R $salary</td>'n
</tr>'n";
}
// change the format of the salary variable
$acc_sal = number_format($total_salary, 2, '.', ',');
echo "</table><br>";
echo "Accumulated Salary for the selected period is<b> R $acc_sal<b>";
?>
一个相当简单的方法是在while
循环之前定义一个新变量(例如$total_salary
),并将每个工资添加到其中。
所以你会有(代码被截断了):
...
$total_salary = 0;
while($row = mysqli_fetch_assoc($result))
{
extract($row);
$sal = ((($employee_salary/180)*$nt)+((($employee_salary/180)*$ot)*1.5)+((($employee_salary/180)*$st)*2));
$salary = number_format($sal, 2, '.', ',');
// add this salary to the total
$total_salary += $sal;
echo "... row markup ...";
}
echo "</table><br>";
echo "<b> Accumulated Salary for the selected period is $total_salary<b>";
附言:这是一个很棒的查询!!!:)
由于您只在一列上使用GROUP BY,请检查WITH ROLLUP选项,它将从数据库中再返回一行。
如果您这样做,请在代码中留下一个巨大的注释,因为未来的开发人员可能不会期望额外的行。