CODEWITHSUNDEEP
phpmysqlsql
user2762748
user2762748

Reputation: 351

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?

Upvotes: 4

Views: 6082

Answers (4)

Said Salomon
Said Salomon

Reputation: 129

its little late, but its easy with a roll back at the end of query

SELECT ingredient_title, ingredient_oz, ingredient_lbs, ingredient_grams
FROM ingredients
WITH ROLLUP

here some examples from other site https://www.mysqltutorial.org/mysql-rollup/

Upvotes: 2

Fluffeh
Fluffeh

Reputation: 33512

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.

Upvotes: 4

Waldz
Waldz

Reputation: 104

Don't be afraid to run 2 separate SQL queries, because:

  • Grouping (for sum) works differently
  • Also code logic will more readable and decoupled
$query = "
  SELECT
    ingredient_title,
    ingredient_oz,
    ingredient_lbs,
    ingredient_grams
  FROM ingredients
";
$ingredients = $db->query($query)->fetch_all();

$query = "
  SELECT
    SUM(ingredient_oz) as oz_sum,
    SUM(ingredient_lbs) as lbs_sum,
    SUM(ingredient_grams) as grams_sum
  FROM ingredients
";
$ingredientSummary = $db->query($query)->fetch_assoc();


<!-- Beginning of table is here -->
<?php
foreach ($ingredients as $ingredientRow) ?>
  <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 $ingredientSummary["oz_sum"]; ?></td>
  <td><?php echo $ingredientSummary["lbs_sum"]; ?></td>
  <td><?php echo $ingredientSummary["grams_sum"]; ?></td>
</tr>
</tfoot>
</table>
<?php }?>

Upvotes: 1

keji
keji

Reputation: 5990

I think you should make it a separate query when selecting the sum. Does it make sense that it can get the sum of each row when the sum is of all the rows? By grouping you cause the SUM to sum each group, and every ingredient is a marked as a group.

Upvotes: 0

Related Questions