Multidimensional array and aggregate functions in

Question

UPDATED

How to display 1 more column next to that u2 called CUMULATIVE TOTAL
it should display total number of students total payable total paid and total due based on counsellors.

Consider i have c1,c2,c3,c4 as counsellors and u1,u2 as universities say c1 has 5 student in each university in that case CUMULATIVE TOTAL column should show total number of students column as [c1][No of students]=10, [c1][Payable]=some value, [c1][Paid]=some value, [c1][Balence]=some value

Please check the following code and let me know is there any way to write select query inside that SUM aggregate function or any alternate solution because i want wll_invoice.total_payable should group by customer_id.

        <?php
define('DB_MAIN', 'localhost|user|passowd|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');
$counselors = $db->fetchAll('SELECT distinct customer_counselor FROM wll_customer');
$payments_ = $db->fetchAll('SELECT
    customer_counselor,
    customer_university,
    COUNT(DISTINCT customer_name) AS \'no of students\',
    SUM(DISTINCT wll_invoice.total_payable) AS payable,**//I want to make total_payable should GROUP BY customer_id** 
    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;');

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

<table id="table_id" class='display table-bordered'>
    <thead>
    <tr>
        <td rowspan="2">Sl</td>
        <td rowspan="2" >counselor</td>
<?php
    foreach ($universities as $key => $university){ ?>

        <td colspan="4" ><?=$university->customer_university ?> </td>
    <?php } ?>
    </tr>
    <tr>
    <?php foreach ( $universities as $university){?>
        <td>no of students</td>
        <td>payable</td>
        <td>paid</td>
        <td>balance</td>
    <?php } ?>
    </tr>
    </thead>
    <tbody>
    <tr>
    <?php foreach ( $counselors as $counselor){?>
    <?php foreach ( $universities as $key => $university){
     $payment = $payments[$counselor->customer_counselor][$university->customer_university];
    ?>  <?php if(!$key){?>
         <td></td>
         <td><?=$counselor->customer_counselor?></td>
        <?php } ?>
        <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 } ?>
    </tr>
    <?php } ?>
    </tbody>
</table>

Answer 1

I hope this is the code you are looking after:

<?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>

I have used following query where I am using Union to append overall data by counsellor as well that you are looking for. Also if you have noticed in code, there I have appended a cumulative university object to the university list to process it same loop.

(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`)

---

Try using this query for distinct value, but you really need to update your schema. This is just a temporary solution:

(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`)

Answer 2

Your SQL should group by customer_university as well as customer_counselor:

SELECT 
customer_counselor, 
customer_university, 
COUNT(customer_name) 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