Многомерный массив и агрегатные функции в MySQL с использованием PHP?

Я надеюсь, что это код, который вы ищете:

<?php
define('DB_MAIN', 'localhost|user|password|database');

class my_db{

private static $databases;
private $connection;

public function __construct($connDetails){
if(!is_object(self::$databases[$connDetails])){
list($host, $user, $pass, $dbname) = explode('|', $connDetails);
$dsn = "mysql:host=$host;dbname=$dbname";
self::$databases[$connDetails] = new PDO($dsn, $user, $pass);
}
$this->connection = self::$databases[$connDetails];
}

public function fetchAll($sql){
$args = func_get_args();
array_shift($args);
$statement = $this->connection->prepare($sql);
$statement->execute($args);
return $statement->fetchAll(PDO::FETCH_OBJ);
}
}

$db = new my_db(DB_MAIN);
$universities = $db->fetchAll('SELECT distinct customer_university FROM wll_customer order by customer_university');
/**
* Adding Cummulative university
*/
$cumulativeUniversity = new StdClass();
$cumulativeUniversity->customer_university = "CUMULATIVE TOTAL";
$universities[] = $cumulativeUniversity;

$counselors = $db->fetchAll('SELECT distinct customer_counselor FROM wll_customer order by customer_counselor');
$payments_ = $db->fetchAll('(SELECT
customer_counselor,
customer_university,
COUNT(distinct wll_invoice.customer_id) AS \'no of students\',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor, customer_university
order by `customer_counselor`, `customer_name`)
UNION
(SELECT
customer_counselor,
"CUMULATIVE TOTAL" as university,
COUNT(distinct wll_invoice.customer_id) AS \'no of students\',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor
ORDER BY `customer_counselor`)');

$payments = [];
foreach ($payments_ as $payment)
$payments[$payment->customer_counselor][$payment->customer_university] = $payment;
?>

<table id="table_id" class='display table-bordered' border="1">
<thead>
<tr>
<td rowspan="2" >Counselor</td>
<?php
foreach ($universities as $key => $university): ?>
<td colspan="4" ><?=$university->customer_university ?> </td>
<?php endforeach ?>
</tr>
<tr>
<?php foreach ( $universities as $university): ?>
<td>no of students</td>
<td>payable</td>
<td>paid</td>
<td>balance</td>
<?php endforeach ?>
</tr>
<?php foreach ( $counselors as $counselor):?>
<tr>
<td>
<?php echo $counselor->customer_counselor;?>
</td>
<?php foreach ( $universities as $key => $university):
$payment = isset($payments[$counselor->customer_counselor][$university->customer_university]) ? $payments[$counselor->customer_counselor][$university->customer_university] : null;
if($payment):?>
<td><?=(int)$payment->{'no of students'}?></td>
<td><?=number_format($payment->payable,0,',','')?></td>
<td><?=number_format($payment->paid,0,',','')?></td>
<td><?=number_format($payment->balance,0,',','')?></td>
<?php else:?>
<td colspan="4"></td>
<?php endif?>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</thead>
</table>

Я использовал следующий запрос, в котором я использую Union для добавления общих данных консультанта, а также того, что вы ищете. Также, если вы заметили в коде, там я добавил к списку университетов накопительный объект университета, чтобы обработать его таким же циклом.

(SELECT
customer_counselor,
customer_university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor, customer_university
ORDER BY `customer_counselor`, `customer_name`)

UNION

(SELECT
customer_counselor,
"CUMULATIVE TOTAL" AS university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(wll_invoice.total_pay) AS paid,
SUM(wll_invoice.due) AS balance
FROM wll_customer
LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_customer.customer_id
GROUP BY customer_counselor
ORDER BY `customer_counselor`)

Попробуйте использовать этот запрос для отдельного значения, но вам действительно нужно обновить схему. Это всего лишь временное решение:

(SELECT
customer_counselor,
customer_university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(final_pay) AS paid,
SUM(wll_invoice.total_payable - final_pay) AS balance
FROM wll_customer

LEFT JOIN (SELECT MAX(id) max_id, customer_id, SUM(total_pay) final_pay FROM `wll_invoice`
GROUP BY customer_id, `total_payable`) AS wll_unique ON wll_unique.customer_id = wll_customer.`customer_id`

LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_unique.customer_id AND `wll_invoice`.id = wll_unique.max_id
GROUP BY customer_counselor, customer_university
ORDER BY `customer_counselor`, `customer_name`)
UNION
(SELECT
customer_counselor,
"CUMULATIVE TOTAL" AS university,
COUNT(DISTINCT wll_invoice.customer_id) AS 'no of students',
SUM(wll_invoice.total_payable) AS payable,
SUM(final_pay) AS paid,
SUM(wll_invoice.total_payable - final_pay) AS balance
FROM wll_customer

LEFT JOIN (SELECT MAX(id) max_id, customer_id, SUM(total_pay) final_pay FROM `wll_invoice`
GROUP BY customer_id, `total_payable`) AS wll_unique ON wll_unique.customer_id = wll_customer.`customer_id`

LEFT JOIN wll_invoice
ON wll_invoice.customer_id = wll_unique.customer_id  AND `wll_invoice`.id = wll_unique.max_id
GROUP BY customer_counselor
ORDER BY `customer_counselor`)