I have this table:
CREATE TABLE Sales (empID INT, yr SMALLINT, sales DECIMAL(10,2));
INSERT sales VALUES
(1, 2005, 12000),(1, 2006, 18000),(1, 2007, 25000),
(2, 2005, 15000),(2, 2006, 6000),(3, 2006, 20000),(3, 2007, 24000);
and this query in mysql:
-
In an inner query, write one aggregating expression per reporting column,
-
In an outer query, build the horizontal sums:
SELECT
IFNULL(empId,‘Totals’) AS EmpId, – outer query labels rollup row
sums.2005, sums.2006, sums.2007, – and calculates horizontal sums
sums.2005 + sums.2006 + sums.2007 AS Sums
FROM ( – inner query groups by employee
SELECT – with an expression for each column
EmpID,
SUM(IF(Yr=2005,sales,0)) As '2005',
SUM(IF(Yr=2006,sales,0)) As '2006',
SUM(IF(Yr=2007,sales,0)) As '2007'
FROM Sales
GROUP BY EmpID WITH ROLLUP
) AS sums;
And want to show the following table:
±-------±---------±---------±---------±----------+
| EmpId | 2005 | 2006 | 2007 | Sums |
±-------±---------±---------±---------±----------+
| 1 | 12000.00 | 18000.00 | 25000.00 | 55000.00 |
| 2 | 15000.00 | 6000.00 | 0.00 | 21000.00 |
| 3 | 0.00 | 20000.00 | 24000.00 | 44000.00 |
| Totals | 27000.00 | 44000.00 | 49000.00 | 120000.00 |
±-------±---------±---------±---------±----------+
With this code:
<?php
$provider = new SqlDataProvider([
'sql' => 'SELECT
IFNULL(empId,"Totals") AS EmpId, -- outer query labels rollup row
sums.2005, sums.2006, sums.2007, -- and calculates horizontal sums
sums.2005 + sums.2006 + sums.2007 AS Sums
FROM ( -- inner query groups by employee
SELECT -- with an expression for each column
EmpID,
SUM(IF(Yr=2005,sales,0)) As `2005`,
SUM(IF(Yr=2006,sales,0)) As `2006`,
SUM(IF(Yr=2007,sales,0)) As `2007`
FROM Sales
GROUP BY EmpID WITH ROLLUP
) AS sums',
//'params' => [':status' => 1],
//'totalCount' => $count,
'pagination' => [
'pageSize' => 10,
],
'sort' => [
'attributes' => [
'EmpID',
//'view_count',
//'created_at',
],
],
]);
// returns an array of data rows
$model = $provider->getModels();
print_r($model);
?>
<!-- Main content -->
<section class="invoice">
<table class="table" >
<tr style= "background-color: #bfcfd2" ><td colspan="14"><b>Total Emp By Year</b></td></tr>
<tr style= "background-color: #F2F2F2"><td>EMP_ID/Year</td>
<td>2005</td>
<td>2006</td>
<td>2007</td>
<td>Total</td> </tr>
<?php
//total colums print with show university
$dataList=ArrayHelper::map($model, 'EmpId', 'EmpId');
foreach ($dataList as $key => $value) {
echo '<tr><td>'.$value.'</td>';
for($i=1;$i<=3;$i++){
echo '<td id='.$value.$i.'>0</td>';
}
echo '<td style= "background-color: #4CAF50" id='.$value.'>0</td></tr>'; //for print row toatal.
}
//bootam row.
//1.Display counts, 2.row total, 3.column total, 4. Grand total.
foreach ($model as $key => $value) {
echo "<script> document.getElementById('".$value['EmpId'].$value['Sums']."').innerHTML = ".$value['Sums']." </script>";
echo "<script> "
. "document.getElementById('".$value['EmpId']."').innerHTML = parseInt(document.getElementById('".$value['EmpId']."').innerHTML) + ". $value['Sums'].";"
. "document.getElementById('sums').innerHTML = parseInt(document.getElementById('sums').innerHTML) + ". $value['Sums']
. "</script>";
}
?>
</table>
</section>
The table is showing only the Totals of years, not the sums for particular months.