Get sum of MySQL column in PHP
You can completely handle it in the MySQL query:
SELECT SUM(column_name) FROM table_name;
Using PDO (mysql_query
is deprecated)
$stmt = $handler->prepare('SELECT SUM(value) AS value_sum FROM codes');
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$sum = $row['value_sum'];
Or using mysqli:
$result = mysqli_query($conn, 'SELECT SUM(value) AS value_sum FROM codes');
$row = mysqli_fetch_assoc($result);
$sum = $row['value_sum'];
How to get the sum in a column using MySQL and PHP
You need to make the SUM() the column in the query :
function calc_tech($day){
include("connect.php"); // include database connection
$res = $mysqli -> query(" SELECT SUM(tech) as sum FROM sales WHERE day = $day ") or die($mysqli->error); // Fetch data from the table
$val = $res -> fetch_array();
$tech_total = $val['sum'];
echo $tech_total; // Echo the result
}
How to follow user id to get sum of MySQL column in PHP?
Lets add alias
to your column and get the column value based on index
.
<?php
$sql_select_point = 'SELECT SUM(total_amount) as tAmount FROM bp_roi WHERE user_id = ' . $user_id;
$query_select_point = db_conn_select($sql_select_point);
foreach($query_select_point as $rs_select_point) {
$bonus_point = $rs_select_point['tAmount'];
}
?>
how to calculate sum of column values using mysql and php?
You have to add $Col_cnt and $row_cnt to check total row & total column
Code:
// Start of printing column names as names of MySQL fields
$col_cnt = 0;
for ($i = 0; $i<mysqli_num_fields($result); $i++) {
$field_info = mysqli_fetch_field($result);
$col = "{$field_info->name}";
echo $col . "\t";
$col_cnt++;
}
print("\n");
// End of printing column names
$row_cnt = 0;
$tot_BalanceAmt = 0;
while($row = mysqli_fetch_assoc($result))
{
$schema_insert = "";
$schema_insert .= $row['col_1'].$sep.$row['col_2'].$sep.$row['col_price'];
$tot_BalanceAmt +=$row['col_price'];
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
$row_cnt ++;
}
$schema_insert = "";
for($i=0;$i<$col_cnt-2;$i++)
{
$schema_insert .= " ".$sep;
}
$schema_insert .= "Total Amount".$sep;
$schema_insert .= $tot_BalanceAmt.$sep;
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print($schema_insert);
print "\n";
PHP SQL SUM column with condition
You need to GROUP BY orderdate
if you want to get the sum for each date. This creates one group for each of the distinct values of orderdate
, and you can then use the aggregate function SUM()
to get each sum for each group (so for you, that's the sum of price for each date).
If you do not supply a GROUP BY orderdate
, you just get the sum across all rows.
SELECT SUM(price) as totalprice, orderdate
FROM table_order
WHERE deptnr='$departmetnnr'
AND orderstatus='CLOSE'
AND orderdate BETWEEN '$oneyearbefore' AND '$currentdate'
GROUP BY orderdate
ORDER BY orderdate
That said, you are currently injecting variables directly into the query, which should be avoided by using a prepared statement.
$stmt = $con->prepare("SELECT SUM(price) as totalprice, orderdate
FROM table_order
WHERE deptnr=?
AND orderstatus='CLOSE'
AND orderdate BETWEEN ? AND ?
GROUP BY orderdate
ORDER BY orderdate");
$stmt->bind_param("sss", $departmetnnr, $oneyearbefore, $currentdate);
$stmt->execute();
$stmt->bind_result($totalprice, $orderdate);
while ($stmt->fetch()) {
$data[] = ["totalprice" => $totalprice, "orderdate" => $orderdate];
}
$stmt->close();
$json = json_encode($data);
You can also use SQL functions to create 1 year in the past and get the current date, instead of using PHP values. Use CURDATE()
to get the current date, then define an interval of 1 year which you subtract from, these will then become the ranges for your BETWEEN
.
$stmt = $con->prepare("SELECT SUM(price) as totalprice, orderdate
FROM table_order
WHERE deptnr=?
AND orderstatus='CLOSE'
AND orderdate BETWEEN CURDATE() AND CURDATE() - INTERVAL 1 YEAR
GROUP BY orderdate
ORDER BY orderdate");
$stmt->bind_param("s", $departmetnnr);
$stmt->execute();
$stmt->bind_result($totalprice, $orderdate);
while ($stmt->fetch()) {
$data[] = ["totalprice" => $totalprice, "orderdate" => $orderdate];
}
$stmt->close();
$json = json_encode($data);
MySql how to select sum column with 2 different where conditions in 1 query
SELECT
account,
SUM(CASE WHEN balance = 'dr' THEN amount
WHEN balance = 'cr' THEN -amount
ELSE 0
END
) amount
FROM
table
GROUP BY
account
calculate sum total of all the figures in a column
mysql_connect($hostname, $username, $password);
mysql_select_db($db);
$sql = "select sum(column) from table";
$q = mysql_query($sql);
$row = mysql_fetch_array($q);
echo 'Sum: ' . $row[0];
Replace variable names and table/column names as needed.
PHP - How to echo the sum of values in a column?
Try this,
$result = mysql_query('SELECT SUM(votes) AS value_sum FROM table_name');
$row = mysql_fetch_assoc($result);
$sum = $row['value_sum'];
With SQLi
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT SUM(votes) AS value_sum FROM table_name";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "sum is : " . $row["value_sum"];
}
} else {
echo "0 results";
}
$conn->close();
Related Topics
How to Enable Curl in PHP/Xampp
Preserve Line Breaks from Textarea
Max Size of Url Parameters in _Get
Login to Remote Site With PHP Curl
List of All Locales and Their Short Codes
How to Minify PHP Page HTML Output
Preserving Session Variables Across Different Domains
I Have an Array of Integers, How to Use Each One in a MySQL Query (In PHP)
Check File Extension in Upload Form in PHP
Tracking the Script Execution Time in PHP
PHPmailer - Smtp Error: Password Command Failed When Send Mail from My Server
How to Filter a Two Dimensional Array by Value