MySQL show all rows and sum at the end

I’ve read through about 20 different answers regarding this question, but either I’m mis-understanding the answers, or its just not clicking. Here is my situation:

I have a table that lists ingredients for a recipe. Columns in the table are: ingredient_id, ingredient_title, ingredient_oz, ingredient_grams, ingredient_lbs (pounds), etc.

I want to list each ingredient, then after all ingredients have been listed, add a final row that sums up all the oz, grams, lbs, etc. Below is an example of the output I am trying to achieve.

Example:

INGREDIENT TITLE    OZ   GRAMS   LBS
ingredient1         4    6       3
ingredient2         1    2       4
ingredient3         9    4       4

TOTAL               14   12      11

My first thought was simply using SUM() AS in the SQL

SELECT ingredient_title, ingredient_oz, ingredient_lbs, ingredient_grams, SUM(ingredient_oz) as oz_sum, SUM(ingredient_lbs) as lbs_sum, SUM(ingredient_grams) as grams_sum FROM ingredients

And here is the code on my page:

<!-- Beginning of table is here -->
<?php 
        while ($ingredientRow = $ingredients->fetch_assoc()) { ?>
          <tr>
            <td><?php echo $ingredientRow["ingredient_title"]; ?></td>
            <td><?php echo $ingredientRow["ingredient_oz"]; ?></td>
            <td><?php echo $ingredientRow["ingredient_lbs"]; ?></td>
            <td><?php echo $ingredientRow["ingredient_grams"]; ?></td>
          </tr>
          <?php } ?>
        </tbody>
        <tfoot>
            <tr>
              <td>TOTALS</td>
              <td><?php echo $ingredientRow["oz_sum"]; ?></td>
              <td><?php echo $ingredientRow["lbs_sum"]; ?></td>
              <td><?php echo $ingredientRow["grams_sum"]; ?></td>
            </tr>
          </tfoot>
      </table>
      <?php }?>

However all that does is return the first row (ingredient 1), and doesn’t return the remaining rows or the sum. Then as I continued to read about this, I saw a low of people discussing using “group by” as well. So then I tried:

SELECT ingredient_title, ingredient_oz, ingredient_lbs, ingredient_grams, SUM(ingredient_oz) as oz_sum, SUM(ingredient_lbs) as lbs_sum, SUM(ingredient_grams) as grams_sum FROM ingredients GROUP BY ingredient_title

That returns all the rows, but again doesn’t return a sum. Am I grouping by the wrong field? Do I need to group each of the fields I’m trying to sum?

Answer

When you run a query, you will get the data back that you ask for, so basically if you run a query to return all the rows individually – you will get those back, without the total. If on the other hand you run a query to get only the sum/totals, you won’t get the individual rows of data.

There are two ways to get what you want. One is done via a query, one is done via PHP itself.

You can write a union query to get the individual rows of data, then return the sums, something like this:

SELECT 
    ingredient_title, 
    ingredient_oz, 
    ingredient_lbs, 
    ingredient_grams
FROM 
    ingredients
union all
SELECT 
    ingredient_title, 
    SUM(ingredient_oz) as oz_sum, 
    SUM(ingredient_lbs) as lbs_sum, 
    SUM(ingredient_grams) as grams_sum 
FROM 
    ingredients

Which will return both.

Or you can write a quick snippet of PHP code to do the addition for you in your code based on the first part of the query:

<?php

    $sql="SELECT 
        ingredient_title, 
        ingredient_oz, 
        ingredient_lbs, 
        ingredient_grams
    FROM 
        ingredients";
    //Execute query:
    while($result)
    {
        echo $result['ingredient_title'];
        echo $result['ingredient_oz'];
        // etc etc. Format as needed...
        $ingOz+=$result['ingredient_oz'];
        $ingLbs+=$result['ingredient_lbs'];
        $ingGrams+=$result['ingredient_grams'];
    }
    // And now the totals:
    echo $ingOz;
    echo $ingLbs;
    // etc etc.
?>

I would personally probably use the second approach – you don’t need to make the database run the query twice just to get the results – and you are already getting all the individual rows of data, therefore you may as well simply keep a simple running total in a variable to be displayed as needed.