• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Create a Cumulative Sum Column in codeigniter

#1
I have this table pc_expenditure
Code:
MariaDB [paper2]> select * from pc_expenditure;


+----------------------+-------------------------+-------------------+---------------+---------------------+
| pc_expenditureID | expensetypesID       | receipt_no       | details         |    amount_spent |
+----------------------+-------------------------+-------------------+---------------+---------------------+
|                        1 |                            1 | 345                 | transport     |                    43 | 
|                        2 |                            2 | 3456               | water          |                    50 |
+----------------------+-------------------------+-------------------+---------------+---------------------+

I'd like to get a  cumulative sum(running total) for each column in codeigniter but it's kind tricky it returns all the values in the same column
I don't know how to go around it.

cumulative sum
Code:
MariaDB [paper2]> select expensetypesID, amount_spent, (@csum := @csum + amount_spent) as cumulative_sum
   -> from pc_expenditure
   -> order by expensetypesID;


+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | cumulative_sum  |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                      43 |
|                      2 |                 50 |                      93 |
+--------------------+------------------+---------------------+

Below is my code in codeigniter
model
PHP Code:
function get_pettycash_amount() {
 
       $this->db->select('amount_spent');
 
       $this->db->from('pc_expenditure');
 
       $this->db->order_by("pc_expenditureID""ASC");
    
$query $this->db->get();
 
       return $query->result(); 

controller
PHP Code:
$this->data['original'] = $this->pc_expenditure_m->get_pettycash_amount($schoolyearID); 

view
PHP Code:
<td data-title="<?=$this->lang->line('pettycash_bal')?>">
 
        <?php
                $total 
= array();
 
              foreach($original as $number)
 
                        {
 
                                           $total[] = array_sum($total)+$number->amount_spent;
 
                        }
 
              $total_expense implode(','$total);
 
                                       
     
echo ($total_expense); ?>
</td> 

the above code display this kind of result;
+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | balance             |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                  43,93|
|                      2 |                 50 |                  43,93|
+--------------------+------------------+---------------------+
Huh which is slightly different from the second table.
Reply

#2
Do you need the individual ID? If not just run SUM on the whole thing.
Code:
SELECT SUM(amount_spent) AS cumulative_sum FROM pc_expenditure;

If you really want your results to look like your query, just run it with.
PHP Code:
$this->db->query('YOUR QUERY HERE'); 

As you don't need to use Query Builder.

Here's a better way to do math.
PHP Code:
$total 0;$total += $number->amount_spent
Reply

#3
(01-08-2018, 01:45 PM)jreklund Wrote: Do you need the individual ID? If not just run SUM on the whole thing.
Code:
SELECT SUM(amount_spent) AS cumulative_sum FROM pc_expenditure;

If you really want your results to look like your query, just run it with.
PHP Code:
$this->db->query('YOUR QUERY HERE'); 

As you don't need to use Query Builder.

Here's a better way to do math.
PHP Code:
$total 0;$total += $number->amount_spent

Well, i like your math buts that's not what i requested...I have tried that before 
and it only yields the sum of  amount_spent. Remember i need the cumulative sum:
Reply

#4
So use option two then. Run your query.
Reply

#5
(01-08-2018, 12:52 PM)kayinja.denis Wrote: I have this table pc_expenditure
Code:
MariaDB [paper2]> select * from pc_expenditure;


+----------------------+-------------------------+-------------------+---------------+---------------------+
| pc_expenditureID | expensetypesID       | receipt_no       | details         |    amount_spent |
+----------------------+-------------------------+-------------------+---------------+---------------------+
|                        1 |                            1 | 345                 | transport     |                    43 | 
|                        2 |                            2 | 3456               | water          |                    50 |
+----------------------+-------------------------+-------------------+---------------+---------------------+

I'd like to get a  cumulative sum(running total) for each column in codeigniter but it's kind tricky it returns all the values in the same column
I don't know how to go around it.

cumulative sum
Code:
MariaDB [paper2]> select expensetypesID, amount_spent, (@csum := @csum + amount_spent) as cumulative_sum
   -> from pc_expenditure
   -> order by expensetypesID;


+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | cumulative_sum  |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                      43 |
|                      2 |                 50 |                      93 |
+--------------------+------------------+---------------------+

Below is my code in codeigniter
model
PHP Code:
function get_pettycash_amount() {
 
       $this->db->select('amount_spent');
 
       $this->db->from('pc_expenditure');
 
       $this->db->order_by("pc_expenditureID""ASC");
    
$query $this->db->get();
 
       return $query->result(); 

controller
PHP Code:
$this->data['original'] = $this->pc_expenditure_m->get_pettycash_amount($schoolyearID); 

view
PHP Code:
<td data-title="<?=$this->lang->line('pettycash_bal')?>">
 
        <?php
                $total 
= array();
 
              foreach($original as $number)
 
                        {
 
                                           $total[] = array_sum($total)+$number->amount_spent;
 
                        }
 
              $total_expense implode(','$total);
 
                                       
     
echo ($total_expense); ?>
</td> 

the above code display this kind of result;
+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | balance             |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                  43,93|
|                      2 |                 50 |                  43,93|
+--------------------+------------------+---------------------+
Huh which is slightly different from the second table.

oh I got where the problem was! I had two foreach loops so this led the display of these two Cumulative Sums in the same  Column
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.