How to calculate the sum of multiple rows of a single column in mysql

Question

Below is my code i am trying to calculate the sum of values in a single column that is: SUM(sum) as total, which i want to print in the of my output table as Total; but that part of the code is not producing the expected output.

<?php
require_once('includes/connect.php');
$result = mysqli_query($con, "SELECT studentid, hw1, hw2, hw3, SUM(hw1+hw2+hw3) as sum, SUM(sum) as total FROM scores GROUP BY studentid");

echo "<table border='1'>
<thead>
<tr>
    <th>StudentID</th>
    <th>HW1</th>
    <th>HW2</th>
    <th>HW3</th>
    <th>SUM</th>
</tr>
</thead>";

echo "<tfoot>
<tr>
    <td>Total:</td>
    <td> echo SUM(sum);</td>
</tr>
</tfoot>";
while ($row = mysqli_fetch_array($result))
{
"<tbody>
  <tr>";
    echo "<td>" . $row['studentid'] . "</td>";
    echo "<td>" . $row['hw1'] . "</td>";
    echo "<td>" . $row['hw2'] . "</td>";
    echo "<td>" . $row['hw3'] . "</td>";
    echo "<td>" . $row['sum'] . "</td>";
    echo "
  </tr>
 </tbody>"; 
}
 echo "</table>";

 mysqli_close($con);
 ?>

I shall appreciate suggestions to resolve this. Thanks.

Khawer Zeshan · Accepted Answer · 2013-12-28 22:34:40Z

1

This is wrong in your query SUM(sum) as total

$result = mysqli_query($con, "SELECT studentid, hw1, hw2, hw3, SUM(hw1+hw2+hw3) as sum, SUM(sum) as total FROM scores GROUP BY studentid");

Should be like this

$result = mysqli_query($con, "SELECT studentid, hw1, hw2, hw3, SUM(hw1+hw2+hw3) as sum FROM scores GROUP BY studentid");

answered Dec 28, 2013 at 22:34

Khawer Zeshan

9,6568 gold badges43 silver badges63 bronze badges

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

1 Comment

Terungwa Over a year ago

The "SUM(hw1+hw2+hw3) as sum" statement is the sum of the three columns h1, h2 and h3, in addition to that, i also want to find the total of each sum row along a single column, that is why i have this second statement "SUM(sum) as total".

Gordon Linoff · Accepted Answer · 2013-12-28 22:54:46Z

0

If the values could be NULL and you want to ignore those values, then you might want:

SELECT studentid, hw1, hw2, hw3,
       (coalesce(sum(hw1), 0) + coalesce(sum(hw2), 0) +coalesce(sum(hw3), 0)) as total
FROM scores
GROUP BY studentid;

answered Dec 28, 2013 at 22:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to calculate the sum of multiple rows of a single column in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related