Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to i sum a column on the last roll of a table in PHP?

I would like to sum the column age as shown on the image below on my table but im not sure how to implement this. i know i would need to use select sum(age) from user but where do i put this?

total sum

I did the below:

if (isset($_POST['date1']) && isset($_POST['date2'])) {
    
    $sql = "SELECT * FROM user WHERE signup_date BETWEEN '".$_POST['date1']."' AND '".$_POST['date2']."'";
    $result = mysqli_query($conn, $sql);
    
    if (mysqli_num_rows($result) > 0) {
        
        $x = 0;
        while ($row = mysqli_fetch_assoc($result)) {

            $output = '<tr>
                            <td>'.++$x.'</td>
                            <td>'.$row["name"].'</td>
                            <td>'.$row["email"].'</td>
                            <td>'.$row["age"].'</td>
                            <td>'.$row["signup_date"].'</td>
                        </tr>';

            echo $output;
        }
Tags:

2

Answers


  1. 0

    you can run a separate query for get sum of age field. like this:

    $sqlSum = "SELECT SUM(age) AS sum_age FROM user WHERE signup_date BETWEEN '".$_POST['date1']."' AND '".$_POST['date2']."'";
$resultSum = mysqli_query($conn, $sqlSum);
$rowSum = mysqli_fetch_assoc($resultSum);
$sum = $rowSum['sum_age'];

//here you can use the $sum variable value

    And if you don’t want to run a new query, change your code like this:

    $sum = 0;
while ($row = mysqli_fetch_assoc($result)) {
    $sum += $row['age'];
    $output = '<tr>
                        <td>'.++$x.'</td>
                        <td>'.$row["name"].'</td>
                        <td>'.$row["email"].'</td>
                        <td>'.$row["age"].'</td>
                        <td>'.$row["signup_date"].'</td>
               </tr>';

    echo $output;
}

//here you can use the $sum variable value
    Login or Signup to reply.
  2. 0

    You can explore these two options:

    1.) Whilst looping through your result, you can decide to sum the values of the ‘age’ column and display the total age value at the end of your while loop

    2.) Write another query on your signup_date table that uses the SUM() SQL function to sum up the age column

    Here we go…

    Option One:

    $ageTotal = 0;

    while ($row = mysqli_fetch_assoc($result)) {

    $ageTotal += $row['age']; //adds each age value to the previous age
$output = '<tr>
               <td>'.++$x.'</td>
               <td>'.$row["name"].'</td>              
               <td>'.$row["email"].'</td>              
               <td>'.$row["age"].'</td>           
               <td>'.$row["signup_date"].'</td>               
           </tr>';
echo $output;

    }

    /*
    You can access the total age from the $ageTotal variable.

    echo $ageTotal;

    */

    Option Two:

    $sqlAgeTotal = "SELECT SUM(age) AS ages FROM user WHERE signup_date BETWEEN '".$_POST['date1']."' AND '".$_POST['date2']."'";

$resultAgeTotal = mysqli_query($conn, $sqlAgeTotal);

$rowAgeTotal = mysqli_fetch_assoc($resultAgeTotal);

$ageTotal = $rowAgeTotal['ages'];

    /*
    You can access the total age from the $ageTotal variable.

    echo $ageTotal;

    */

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search