PHP – MYSQL : sum() operation

Last Updated : 01 Oct, 2021

Problem Statement:

In this article, we are going to perform sum() aggregate operation on our database using PHP with xampp server.

So we are considering the food_order database and perform database sum() operation.

Requirements:

xampp

Introduction:

PHP stands for hypertext preprocessor which is a server-side scripting language connect with databases.

it is connected with MySQL to perform manipulations in a database.

MySQL is a database query language for managing databases.

sum()

sum() function is an aggregation operation used to add the particular column based on the given condition.

Syntax:

SELECT SUM(column1),column2,...column1 from
table_name;

It can be also used with GROUP BY & HAVING clause.

Example query:

Consider the table:

Find the total cost of food items

SELECT  SUM(cost) from food;

Output:

Total cost: 2935

Find the total weight of food items

SELECT  SUM(weight) from food;

Total weight: 382

Find the total cost of food items with respect to items

SELECT food_item, SUM(cost) FROM food GROUP BY food_item;
Total cakes = 1365
Total chocos = 200
Total chocoss = 705
Total fry = 345
Total milk = 320

Approach

Create a database
Create a table
PHP code to perform sum() operation

Steps

Start xampp server

Create a database named geek and create a table named food

Table structure:

Refer this for how to insert records in xampp

https://www.geeksforgeeks.org/performing-database-operations-in-xampp/

Finally, the food table contains

PHP code to perform sum function

form.php

After writing code type “localhost/form.php” to view the output in the browser.

PHP

<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//geek is the database name
$dbname = "geek";
 
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "sum() aggregate  Demo "; echo"</h1>";
echo "<h2>";echo " Find the total cost of food items ";echo "</h2>";
//sql query
$sql = "SELECT  SUM(cost) from food";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " Total cost: ". $row['SUM(cost)'];
    echo "<br>";
}
 
echo "<h2>";echo " Find the total weight of food items ";echo "</h2>";
//sql query
$sql = "SELECT  SUM(weight) from food";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " Total weight: ". $row['SUM(weight)'];
    echo "<br>";
}
 
 
//close the connection
 
$conn->close();
?>
</body>
</html>

Output:

Example: sum() with group by clause

form1.php

PHP

<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//geek is the database name
$dbname = "geek";
 
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "sum() aggregate with group by Demo "; echo"</h1>";
echo "<h2>";echo " 
 ";echo "</h2>";
//sql query
$sql = "SELECT food_item, SUM(cost) FROM food GROUP BY food_item";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo "Total ". $row['food_item']. " = ". $row['SUM(cost)'];
      echo "<br />";
}
//close the connection
 
$conn->close();
?>
</body>
</html>