how to calculate sum of column values using mysql and php?

Question

How to count the sum of balance amount(bal_amt db field name) where status column is null and display it in end of the excel sheet.How to calculate sum value of the mysql column Please help me to do this



      <?php
      include_once('dbconnect.php');
      date_default_timezone_set('Asia/Dubai');
      $xls_filename = 'Report_'.date('d-m-Y').'.xls'; // Define Excel (.xls) file name


      $sql = "SELECT a.cusName as CustomerName, a.cusMob as CustomerMob, a.invoiceNo as InvoiceNo,
                    a.invoiceDate as InvoiceDate, a.SPH_right, a.CYL_right,
                    a.Axis_right, a.Prism_right,a.Add_right,
                    a.SPH_left, a.CYL_left, a.status,
                    a.Axis_left, a.Prism_left,a.Add_left, 
                    CONCAT_WS(',', IF(a.PD_right IS NULL OR a.PD_right = '', '0', a.PD_right), IF(a.PD_left IS NULL OR a.PD_left = '', '0', a.PD_left)) as PD_RL,
                    a.total_VAT as TotalVAT, a.bill_tot as BillTot,
                    a.adv_amt as AdvanceAmt, 
                    a.bal_amt as BalanceAmt,
                    b.itemsName as Items,b.rate as Rate, b.amt_vat as ItemVat,
                    ROUND(b.amt_vat + b.amount, 2) as Amount
                    FROM invoices a, invoice_items b where a.invoiceID=b.invoiceid and  a.status IS NULL
                    and a.invoiceDate between '$getfromdate' and '$gettodate' 
                    order by a.invoiceID desc";

      $result = mysqli_query($link, $sql);



      // Header info settings
      header("Content-Type: application/xls");
      header("Content-Disposition: attachment; filename=$xls_filename");
      header("Pragma: no-cache");
      header("Expires: 0");

      /***** Start of Formatting for Excel *****/
      // Define separator (defines columns in excel &amp; tabs in word)
      $sep = "\t"; // tabbed character
        $col_cnt = 0;
      // Start of printing column names as names of MySQL fields
      for ($i = 0; $i<mysqli_num_fields($result); $i++) {
        $field_info = mysqli_fetch_field($result);
        $col = "{$field_info->name}";
        echo $col . "\t";
         $col_cnt++;
      }
      print("\n");
      // End of printing column names

      // Start while loop to get data
      $row_cnt = 0;
      $tot_BalanceAmt = 0;
      while($row = mysqli_fetch_row($result))
      {
        $schema_insert = "";
        $tot_BalanceAmt +=$row['BalanceAmt'];
        for($j=0; $j<mysqli_num_fields($result); $j++)
        {
          if(!isset($row[$j])) {
            $schema_insert .= " ".$sep;
          }
          elseif ($row[$j] != "") {
            $schema_insert .= "$row[$j]".$sep;
          }
          else {
            $schema_insert .= "".$sep;
          }
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
         $row_cnt ++;
      }
      print "\n";

      $schema_insert = "";
      for($i=0;$i<$col_cnt-25;$i++)
      {
        $schema_insert .= " ".$sep;
      }
      $schema_insert .= "TOTAL BALANCE = ".$sep;
      $schema_insert .= $tot_BalanceAmt.$sep;
      $schema_insert = str_replace($sep."$", "", $schema_insert);
      $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
      $schema_insert .= "\t";
      print($schema_insert);
      print "\n";


    ?>

I want to display total balance amount ``` db column name BalanceAmt ``` at end of the   excel sheet and result should be like this

    bal_amt       status

    1        

    1             cancelled

    3        

    4             cancelled

    0

    3

    0
    3

Total Balance amount : 10 (only need to calculate sum of bal_amt where status field null)

Ashu · Accepted Answer · 2019-01-29 09:09:37Z

1

You have to add $Col_cnt and $row_cnt to check total row & total column

Code:

// Start of printing column names as names of MySQL fields
  $col_cnt = 0;
  for ($i = 0; $i<mysqli_num_fields($result); $i++) {
    $field_info = mysqli_fetch_field($result);
    $col = "{$field_info->name}";
    echo $col . "\t";
    $col_cnt++;
  }
  print("\n");
  // End of printing column names

  $row_cnt = 0;
  $tot_BalanceAmt = 0;
  while($row = mysqli_fetch_assoc($result))
  {
    $schema_insert = "";
    $schema_insert .= $row['col_1'].$sep.$row['col_2'].$sep.$row['col_price'];
    $tot_BalanceAmt +=$row['col_price'];
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
    $row_cnt ++;
  }

  $schema_insert = "";
  for($i=0;$i<$col_cnt-2;$i++)
  {
    $schema_insert .= " ".$sep;
  }
  $schema_insert .= "Total Amount".$sep;
  $schema_insert .= $tot_BalanceAmt.$sep;
  $schema_insert = str_replace($sep."$", "", $schema_insert);
  $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
  $schema_insert .= "\t";
  print($schema_insert);
  print "\n";

edited Jan 29, 2019 at 9:09

answered Jan 29, 2019 at 5:12

Ashu

1,3102 gold badges10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samuel Liew Over a year ago

Comments are not for extended discussion; this conversation has been moved to chat.

Collectives™ on Stack Overflow

how to calculate sum of column values using mysql and php?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related