Sum row totals on Sum_Total column

Beginners - Learning PHP
#1

I need to sum the sum_total column. And echo result. See pic Month Total 100

<?php
//filter_weekly_activity_bydate.php
  {
          require 'db_connection2.php';
          
             $stmt = $pdo->prepare( " SELECT *,
          
ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) as sum_total

FROM weekly_activity_count

WHERE
month(date) = ?
AND
year(date) = ?

GROUP BY date desc
                 ");
                 
$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                <tr>  
                              
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Sum Total</span></th>
                              <th width="10%"><span>Date</span></th>
                              
                  </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["sum_total"] .'</td>
                          <td>'. $row["date"] .'</td>
			          </tr>  
                ';
            }
      
       $output .= '</table>'; 
        
        }
         echo $output;
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

weeklyact
weeklyact1895Ă—525 37.4 KB

#2

And what isn’t it doing?

#3

I edited with paint the Month Total 100. I cant figure what code to add to get the Month total result. Like sum(sum total)column?

#4

Since it isn’t within the table structure. You could make another query to just get the count. Or you could iterate thru the values once and add the sums, then iterate through it again to populate the table.

#5

Or just add a second select query, loosely something like this:

SELECT *, SUM(sum_total) AS final_total FROM
( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) AS sum_total
FROM weekly_activity_count
WHERE month(date) = ?
AND year(date) = ?
GROUP BY date desc )

This would run your original query plus sum the total of the results from it… ( Not tested )
You don’t need to name the second select since you are not using it by name, just process it first,
then use it’s results.

#6

I’m trying to get on the right track, but I cant get this code to work, tried a couple ways.
What am I be missing?

 <?php

  {
          require 'db_connection2.php';
          
   $stmt = $pdo->prepare(" SELECT
 
   SELECT *, SUM(sum_total) AS final_total FROM
( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS sum_total

FROM weekly_activity_count

WHERE
month(date) = ?
AND
year(date) = ?

");
 
$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$sum = final_total;
echo "Month Total :$sum ";   

     
     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                <tr>  
                              
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Play Praise Nursery Area</span></th>
                              <th width="10%"><span>Childrens Worship Area</span></th>
                              <th width="10%"><span>Sum Total</span></th>
                              <th width="10%"><span>Date</span></th>
                              
                  </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["play_praise_nursery_count"] .'</td>
                          <td>'. $row["childrens_worship_count"] .'</td> 
                          <td>'. $row["sum_total"] .'</td>
                          <td>'. $row["date"] .'</td> 
			          </tr>  
                ';
            }
      
       $output .= '</table>'; 
        
        }
         echo $output;
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>
#7

This isn’t referencing the result set, and should induce an error because it is a string literal, not a constant variable.

#8

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$sum = final_total;
echo "Month Total :$sum ";

Try something more like $sum=$result[“final_total”];

#9

Ok added error report, didn’t know I could. I cant figure out the fix for the error. I googled a bunch of possible fixes but none worked. I’m stumped.

 <?php
 //filter_weekly_activity_bydate.php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
  {
          require 'db_connection2.php';
          
$stmt = $pdo->prepare(" SELECT *, SUM(sum_total) AS final_total FROM
( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS sum_total
FROM weekly_activity_count
WHERE month(date) = ?
AND year(date) = ?
GROUP BY date desc )         
");

$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$sum=$result[“final_total”];
echo "Month Total :[$sum] ";


     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                      
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Play Praise Nursery Area</span></th>
                              <th width="10%"><span>Childrens Worship Area</span></th>
                              <th width="10%"><span>Sum Total</span></th>
                              <th width="10%"><span>Date</span></th>
                              
                  </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["play_praise_nursery_count"] .'</td>
                          <td>'. $row["childrens_worship_count"] .'</td> 
                          <td>'. $row["sum_total"] .'</td>
                          <td>'. $row["date"] .'</td> 
			          </tr>  
                ';
            }
      
        $output .= '</table>'; 
        
        }
         echo $output;
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>`

ERROR REPORT
Fatal error : Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1248 Every derived table must have its own alias in /home/spir/public_html/ethro/dropdown-list/filter_weekly_activity_bydate.php:12 Stack trace: #0 /home/spir/public_html/ethro/dropdown-list/filter_weekly_activity_bydate.php(12): PDO->prepare(’ SELECT *, SUM(…’) #1 {main} thrown in /home/spir/public_html/ethro/dropdown-list/filter_weekly_activity_bydate.php on line 12

line 12 >> $stmt = $pdo->prepare(" SELECT *, SUM(sum_total) AS final_total FROM

#10

Sorry that was my fault. You do need to assign the second SELECT a name. Try it this way. Not sure on this 100%, but, should work.

$stmt = $pdo->prepare(" SELECT *, SUM(sum_total) AS final_total FROM
( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS sum_total
FROM weekly_activity_count
WHERE month(date) = ?
AND year(date) = ?
GROUP BY date desc )  ABC       
");

The ABC is the new name/alias for the first SELECT. You might need to also use SUM(ABC.sum_total) depending on your MySQL set up. Not sure…

#11

Tried SUM(sum_total) and SUM(ABC.sum_total)

 <?php
 //filter_weekly_activity_bydate.php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

  {
          require 'db_connection2.php';

        }
          
       $stmt = $pdo->prepare(" SELECT *, SUM(ABC.sum_total) AS final_total
FROM
 ( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS sum_total

FROM weekly_activity_count
WHERE
month(date) = ?
AND
year(date) = ?
GROUP BY date desc ) ABC

");

$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$sum = $result["final_total"];
echo "Month Total :$sum ";
$output=0;
     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                      
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Play Praise Nursery Area</span></th>
                              <th width="10%"><span>Childrens Worship Area</span></th>
                              <th width="10%"><span>Sum Total</span></th>
                              <th width="10%"><span>Date</span></th>
                              
                  </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["play_praise_nursery_count"] .'</td>
                          <td>'. $row["childrens_worship_count"] .'</td> 
                          <td>'. $row["sum_total"] .'</td>
                          <td>'. $row["date"] .'</td> 
			          </tr>  
                ';
            }
      
        $output .= '</table>';        
        
         echo $output;
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

report1
report11830Ă—414 46 KB

I’m getting the report now, except the error, month total is 0 and is missing 2 other rows?

Notice : Undefined index: final_total in /home/holyspir/public_html/jethro/dropdown-list/filter_weekly_activity_bydate.php on line 27

Line 27 >> $sum = $result[“final_total”];

table
table1660Ă—355 31.9 KB

Thank you for your continued valuable time and help.

#12

Go into your database control panel like you showed in the last display.
Select the weekly_activity_count table.
Select the SQL option at the top of it.
Enter this first SELECT clause in the SQL and press GO to see what results you are getting.
This will tell us if that part of the query is working. Then, do it a second time with the full query.
*** PLEASE NOTE: You will need to put in a month and year instead of the question marks.
You need to find out which part is failing…

#13

sumtotal1
sumtotal11875Ă—537 53 KB

Shows all 3 rows.

finaltotalsql
finaltotalsql1907Ă—427 59.9 KB

It works this way. But only 1 date row. Not all 3

And it works by adding the final_total to the table fields. But only 1 row not 3.

 //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                      
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Play Praise Nursery Area</span></th>
                              <th width="10%"><span>Childrens Worship Area</span></th>
                              <th width="10%"><span>Sum Total</span></th>
                              <th width="10%"><span>Date</span></th>
                              <th width="10%"><span>Final Total</span></th>
                              
                  </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["play_praise_nursery_count"] .'</td>
                          <td>'. $row["childrens_worship_count"] .'</td> 
                          <td>'. $row["sum_total"] .'</td>
                          <td>'. $row["date"] .'</td> 
                          <td>'. $row["final_total"] .'</td>
			          </tr>  
                ';
            }
      
        $output .= '</table>';

finaltotalsql3
finaltotalsql31903Ă—523 56.6 KB

But, I don’t want a column final_total on the rows. Just a Month Total at the top of the report.
But the Month Total is: 0

#14

I’m still not able to get the Month Total: to show correctly or at all.
But, I also really need it to show all the dates in the month, not just the latest date only. It shows all the dates until I add $stmt = $pdo->prepare(" SELECT *, SUM(ABC.sum_total) AS final_total to the code, as you can see in the above pics. Is the final total column field I added to the table, stopping it from showing all the months dates?

#15

You choose what to display in the table.

#16

I know I can remove the final total from the table, but currently it’s the only way it sums the sum total.

#17

I mean,

$sum_total = 0;
$output .= '  
           <table class="table table-bordered">  
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Play Praise Nursery Area</span></th>
                              <th width="10%"><span>Childrens Worship Area</span></th>
                              <th width="10%"><span>Date</span></th>
                              <th width="10%"><span>Final Total</span></th>     
                  </tr>';
foreach($result as $row)       
{
     $sum_total += $row["sum_total"];
    $output .= '  
                  <tr>  
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["play_praise_nursery_count"] .'</td>
                          <td>'. $row["childrens_worship_count"] .'</td> 
                          <td>'. $row["date"] .'</td> 
                          <td>'. $row["final_total"] .'</td>
			          </tr>';
            }      
        $output .= '</table>';

Now $sum_total holds the total values passed through it, and the table doesn’t contain that value any longer.

#18

First thank you for the reply, but I made an error when I said," but currently it’s the only way it sums the sum total" I meant to say displays the final_total. I want the rows to show sum of each row [“sum_total”], but I need to figure the code to echo Month Total: final_total on the output data page, for the month & year chosen from the user input page. currently January 2019.

Also only getting the latest date to show up, not the other 2 dates.

The picture is done with $stmt = $pdo->prepare( " SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) as sum_total
FROM weekly_activity_count
only.
If I add SELECT *, SUM(sum_total) AS final_total FROM
It will only show one date.

weeklyact
weeklyact1895Ă—525 37.4 KB

I took a screen shot of the user input page after submit. and used windows paint to add the Month Total 100, so you could see what I need.

#19

Well, I have spent hours trying ways to get a total and then a grand total. Seems like the best way it to just do it in two different queries. So, you would need to create your one query to create your grand final total. Then display it and then do a second query to handle the displays of the items including the sub total.

Sorry, I could not get all of the lines of data show up along with the grand total. Two queries are so much simpler… Try it that way…

1 Like
#20

That’s It!! Works Perfect! Thankyou!
Except I have the error still.

 <?php
 //filter_weekly_activity_bydate.php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

  {
          require 'db_connection2.php';
          
       
       $stmt = $pdo->prepare("
 DELETE t1 FROM weekly_activity_count t1
        INNER JOIN
    weekly_activity_count t2 
WHERE
    t1.id < t2.id AND t1.date = t2.date");
    
 $stmt->execute();            

        }
          
       $stmt = $pdo->prepare(" SELECT *, SUM(S.sum_total) AS final_total
FROM
 ( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS sum_total

FROM weekly_activity_count
WHERE
month(date) = ?
AND
year(date) = ?

GROUP BY date desc ) S

");

$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

     //-----------------------------Table------------------------------------// 
$sum_total = 0;
$output .= ' 

         <tr>  
                              <th width="10%"><span>Month Total:</span></th>
                            
                  </tr>';
foreach($result as $row)       
{
     $sum_total += $row["sum_total"];
    $output .= '  
                  <tr> 
                          
                          <td>'. $row["final_total"] .'</td>
			          </tr>  
                ';
            }
      
        $output .= '</table>'; 
  $stmt = $pdo->prepare(" SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS sum_total

FROM weekly_activity_count
WHERE
month(date) = ?
AND
year(date) = ?

GROUP BY date desc

");

$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

     //-----------------------------Table------------------------------------// 
$sum_total = 0;
$output .= ' 
           <table class="table table-bordered">  
                      
                     <tr>  
                              <th width="10%"><span>Left Section</span></th>
                              <th width="10%"><span>Center Section</span></th>
                              <th width="10%"><span>Right Section</span></th>
                              <th width="10%"><span>All Other Section</span></th>
                              <th width="10%"><span>Youth Balcony Section</span></th>
                              <th width="10%"><span>Play Praise Nursery Area</span></th>
                              <th width="10%"><span>Childrens Worship Area</span></th>
                              <th width="10%"><span>Sum Total</span></th>
                              <th width="10%"><span>Date</span></th>
                              
                              
                  </tr>';
foreach($result as $row)       
{
     $sum_total += $row["sum_total"];
    $output .= '  
                  <tr> 
                          <td>'. $row["left_count"] .'</td> 
                          <td>'. $row["center_count"] .'</td>
                          <td>'. $row["right_count"] .'</td> 
                          <td>'. $row["allother_count"] .'</td>
                          <td>'. $row["youthbalcony_count"] .'</td>  
                          <td>'. $row["play_praise_nursery_count"] .'</td>
                          <td>'. $row["childrens_worship_count"] .'</td> 
                          <td>'. $row["sum_total"] .'</td>
                          <td>'. $row["date"] .'</td> 
                          
			          </tr>  
                ';
            }
      
        $output .= '</table>'; 
        
         echo $output;
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

Screen Shot

activity1
activity13817Ă—1441 228 KB

Line 41 >> $output .= ’

Sponsor our Newsletter | Privacy Policy | Terms of Service