Sum row totals on Sum_Total column


#21

A union would work, if doing a single query is really needed, but two is simpler.


#22

Astonecipher, show us a sample of how to do that. I would like to use it in another site, well, maybe…
I attempted a union in a couple tests, but, could not get it to show the multiple rows along with the correct
grand total. It did show the grand total in a last col but, it was not correct. I would like to see an example.
(My week point is unions for sure!)


#23

Willymec, that error is due to the code you used. You used .= which expects to append to a variable.

So, $output .= “something” needs to have $output already existing. Add $output=""; at the beginning somewhere without the period so you start with a valid variable $output but with empty contents. Then, when the code hits the append-to-$output, it will work correctly. Hope that makes sense!


#24

I can, but I need the query being used, with the columns. @willymec, you ALWAYS want to specify columns in a query, the only exception to this is testing or when you know what you will get back due to a sub query. So, this is okay,

SELECT * FROM (
    SELECT column1, column2, column3 FROM table_abc WHERE id = 5
)

Because we know that we will get back column1, 2, and 3, even if we change the structure of the table.


#25
SELECT column1, column2, column3 FROM table_abc WHERE id = 5

This does pull the three cols and displays them with all the rows.
BUT,

SELECT *, SUM(sum_total) AS grand_total FROM (
    SELECT column1, column2, column3, SUM(column1+column2+column3) AS sum_total FROM table_abc WHERE id = 5
)

only shows one row which includes the three cols, sum_total and grand_total, but, not all of the rows!


#26

ErnieAlex, When I add $output=""; It removes the error, but also removes the Final Total query results. I am missing something here.


#27

Yes! You need to put that line way up before your queries.

In other words, you must initialize the variable starting out before you fill it with anything.
NOT instead of the line you have now, but, in addition to and before you run any queries…

OR just remove the period. Normally, when you are creating HTML code from PHP commands, you would start with nothing like $output = ‘’; and, then add parts of the HTML code as needed using the .= instead of the =…

In your code, you only show us creating it twice. So, the first place should not have the period and the others after that one should include it. Do you understand that?

Example:
$output = “xyz”; Variable now is zyz
$output = “abc”; Variable now is abc
$output .= “xyz”; Variable now is abcxyz (note it is APPENDED to the previous values…)

Hope that explains it better!


#28

That was a comment on doing select * in general, not for the union question. I still need to column names from that, because you need to account for the columns returned.


#29

Ok no more error’s and I cleaned up some of the code. Change sum to week, and final to month.

 <?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(w.week_total) AS month_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 week_total

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

GROUP BY date desc ) w

");

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

     //-----------------------------Table------------------------------------// 
$week_total = 0;
$output = "";
$output .= ' 
              <table>   ';
foreach($result as $row)  
$output .= '    <th>Month Total:</th>  
                <td>'. $row["month_total"] .'</td> ';
$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 week_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------------------------------------// 
$week_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>WeekTotal</span></th>
                              <th width="10%"><span>Date</span></th>
                              
                              
                  </tr>';
foreach($result as $row)       
{
     $week_total += $row["week_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["week_total"] .'</td>
                          <td>'. $row["date"] .'</td> 
                          
			          </tr>  
                ';
            }
      
        $output .= '</table>'; 
        
         echo $output;
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

activity1

Thank you all for all your time and expert help. I have learned even more. Thank you for being patient also.

Now astonecipher I’m not sure if you are asking me for something?


#30

I don’t have any data to play with, but this should give you every thing in a single return.

SELECT 
	left_count,
	center_count,
	right_count,
	allother_count,
	youthbalcony_count,
	play_praise_nursery_count,
	childrens_worship_count,
	0
FROM 
	weekly_activity_count
UNION
SELECT 
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	(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 week_total
FROM 
	weekly_activity_count	
WHERE
	month(date) = ?
	AND
	year(date) = ?

#31

Getting error

Error

SQL query:

``

SELECT 
	left_count,
	center_count,
	right_count,
	allother_count,
	youthbalcony_count,
	play_praise_nursery_count,
	childrens_worship_count,
	0
FROM 
	weekly_activity_count
UNION
SELECT 
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	(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 week_total
FROM 
	weekly_activity_count	
WHERE
	month(date) = 1
	AND
	year(date) = 2019 LIMIT 0, 25

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS week_total FROM weekly_activity_count WHERE month(date) = 1 AND y' at line 27

Line 27 >> ifnull(childrens_worship_count, 0) AS week_total


#32
SELECT 
	left_count,
	center_count,
	right_count,
	allother_count,
	youthbalcony_count,
	play_praise_nursery_count,
	childrens_worship_count,
	0
FROM 
	weekly_activity_count
UNION
SELECT 
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	(
         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 week_total
FROM 
	weekly_activity_count	
WHERE
	month(date) = 1
	AND
	year(date) = 2019 LIMIT 0, 25

I missed a closing parenthesis


#33

The week totals are not lining up correctly, they made new rows.

join