title: Total sum of several columns in a table
body: <p>I am currently having problem with my <strong>total</strong>. Well, I have 3 different tables. <strong>billing_payments</strong>, <strong>billing_entry</strong>, and <strong>services</strong>.</p>
  
  <p>My problem is, I can't get the total of each <strong>SERVICES</strong>. I provided a screenshot so that you'll get what I mean. </p>
  
  <p><a href="https://i.stack.imgur.com/M8vlJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M8vlJ.png" alt="enter image description here"></a></p>
  
  <p>Here is the code of the report. I have added some comments to indicate where the problem starts and ends.</p>
  
  <pre><code>&lt;table class="table table-striped table-hover"&gt;
                &lt;thead&gt;
                  &lt;tr&gt;
                  &lt;th&gt;#&lt;/th&gt;                    
                  &lt;th&gt;PATIENT NAME&lt;/th&gt;
                  &lt;th&gt;CASE&lt;/th&gt;                                    
                  &lt;?php
                              $servicesquery = $this-&gt;db-&gt;get('services');
                              foreach ($servicesquery-&gt;result() as $service) {
                                echo '&lt;th&gt;'.$service-&gt;service_name.'&lt;/th&gt;';
                              }
                  ?&gt;
                  &lt;th&gt;MEDICAL SUPPLIES&lt;/th&gt;
                  &lt;th&gt;PHILHEALTH&lt;/th&gt;
                  &lt;th&gt;DISCOUNT&lt;/th&gt;
                  &lt;th&gt;GROSS&lt;/th&gt;
                  &lt;th&gt;NET&lt;/th&gt; 
                  &lt;tr&gt;
                &lt;/thead&gt;
                    &lt;tbody&gt;
  
                       &lt;?php
                              $x = 1;
                              $billquery = $this-&gt;db-&gt;query('SELECT * FROM `billing_payments` WHERE (`date` BETWEEN "'.$this-&gt;input-&gt;get('from').'" AND "'.$this-&gt;input-&gt;get('to').'")');
                              foreach ($billquery-&gt;result() as $data) {
                                  echo '&lt;tr&gt;';
                                  echo '&lt;td&gt;'.$x++.'&lt;/td&gt;';
                                  echo '&lt;td&gt;'.$data-&gt;patientname.'&lt;/td&gt;';
                                  echo '&lt;td&gt;'.$data-&gt;session_id.'&lt;/td&gt;';
                                   //SERVICES
                                        $servicesquery = $this-&gt;db-&gt;get('services');
                                  foreach ($servicesquery-&gt;result() as $service) {                                     
  
                                        $this-&gt;db-&gt;where('billing_serviceid', $service-&gt;service_id);
                                        $this-&gt;db-&gt;where('billing_patientid', $data-&gt;patient_id);
                                        $this-&gt;db-&gt;where('billing_id', $data-&gt;billing_id);
                                        $this-&gt;db-&gt;select_sum('billing_amount');
                                        $billing = $this-&gt;db-&gt;get('billing_entry');
                                        foreach ($billing-&gt;result() as $bill) {
                                          echo '&lt;td&gt;'.$bill-&gt;billing_amount.'&lt;/td&gt;';
                                        }
                                  }
                                    //MEDICAL SUPPLIES
                                        $this-&gt;db-&gt;where('billing_id', $data-&gt;billing_id);
                                        $this-&gt;db-&gt;where('billing_servicename', 'MEDICAL SUPPLIES');
                                        $this-&gt;db-&gt;select_sum('billing_amount');
                                        $medsup = $this-&gt;db-&gt;get('billing_entry');
                                        foreach ($medsup-&gt;result() as $med) {
                                          echo '&lt;td&gt;'.$med-&gt;billing_amount.'&lt;/td&gt;';
                                        }
  
                                    //PHILHEALTH    
                                        $this-&gt;db-&gt;where('billing_id', $data-&gt;billing_id);                                          
                                        $this-&gt;db-&gt;select_sum('billing_philhealth');
                                        $philhealth = $this-&gt;db-&gt;get('billing_entry');
                                        foreach ($philhealth-&gt;result() as $phil) {
                                          echo '&lt;td class="bg-info"&gt;'.$phil-&gt;billing_philhealth.'&lt;/td&gt;';
                                        }
  
                                    //DISCOUNT
                                        $this-&gt;db-&gt;where('billing_id', $data-&gt;billing_id);                                          
                                        $this-&gt;db-&gt;select_sum('billing_discount');
                                        $philhealth = $this-&gt;db-&gt;get('billing_entry');
                                        foreach ($philhealth-&gt;result() as $phil) {
                                          echo '&lt;td class="bg-info"&gt;'.$phil-&gt;billing_discount.'&lt;/td&gt;';
                                        }
  
                                    //GROSS
                                        $this-&gt;db-&gt;where('billing_id', $data-&gt;billing_id);                                          
                                        $this-&gt;db-&gt;select_sum('billing_amount');
                                        $gross = $this-&gt;db-&gt;get('billing_entry');
                                        foreach ($gross-&gt;result() as $gr) {
                                          echo '&lt;td class="bg-warning"&gt;'.$gr-&gt;billing_amount.'&lt;/td&gt;';
                                        }
  
  
  
                                    echo '&lt;td class="bg-danger"&gt;'.$data-&gt;total_amount.'&lt;/td&gt;';
                                  echo '&lt;/tr&gt;';
                              }
  
  
  
  
  
  
                              echo '&lt;tr&gt;';
                              echo '&lt;td colspan="3" style="text-align:right"&gt;&lt;strong&gt;TOTAL:&lt;/strong&gt;&lt;/td&gt;';
  
  
                              //PROBLEM STARTS HERE
                              //TOTAL PER SERVICES
  
                                  $quer = $this-&gt;db-&gt;get('services');
                                  foreach ($quer-&gt;result() as $service) {                                                              
                                        $totserv = $this-&gt;db-&gt;query('SELECT * FROM `billing_payments` WHERE (`date` BETWEEN "'.$this-&gt;input-&gt;get('from').'" AND "'.$this-&gt;input-&gt;get('to').'")');                                                                                    
                                        foreach ($totserv-&gt;result() as $servdata) {
                                        $id = $servdata-&gt;id;   
  
                                              $this-&gt;db-&gt;where('billing_id', $servdata-&gt;billing_id);  
                                              $this-&gt;db-&gt;where('billing_serviceid', $service-&gt;service_id);                                   
                                              $this-&gt;db-&gt;select_sum('billing_amount');
                                              $medsup = $this-&gt;db-&gt;get('billing_entry');
                                              foreach ($medsup-&gt;result() as $med) {
                                                echo '&lt;td class="bg-success"&gt;'.$med-&gt;billing_amount.'&lt;/td&gt;';
                                              }
  
                                        }
  
                              }
  
                              //PROBLEM ENDS HERE
  
  
  
  
  
                              //TOTAL NET
                              $totalamt = $this-&gt;db-&gt;query('SELECT SUM(total_amount) AS totalamount FROM `billing_payments` WHERE (`date` BETWEEN "'.$this-&gt;input-&gt;get('from').'" AND "'.$this-&gt;input-&gt;get('to').'")');
                              foreach ($totalamt-&gt;result() as $data) {
                                echo '&lt;td&gt;'.$data-&gt;totalamount.'&lt;/td&gt;';
                              }
                              echo '&lt;/tr&gt;';
  
                       ?&gt;       
  </code></pre>