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
}
Sum of Column in PHP
This is not a direct answer/solution to your immediate PHP problem, which some guru might address, but if you want to sum a SQL column, you would probably be better off doing this in SQL:
SELECT SUM(OrderPrice) AS order_price_sum FROM PizzaOrder
how to get sum of a column with codeigniter query
use as => $data['total']
$data = $this->db
->query("Select SUM(tb1.amount) as total from table1 tb1 inner join table2 tb2 on tb2.Id=tb1.Id Where tb2.Status='1'")
->row_array()
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);
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.
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'];
}
?>
select with sum of column values grouping by day
In the 1st case you need to group by day using the DATE()
function:
SELECT
DATE(datetime) day,
COUNT(*) orderscounter,
SUM(order_value) valuesum,
SUM(pieces) piecessum
FROM orders
GROUP BY day
In the 2nd case you need to group by year/month using the YEAR()
and MONTH()
functions:
SELECT
YEAR(datetime) year,
MONTH(datetime) month,
COUNT(*) orderscounter,
SUM(order_value) valuesum,
SUM(pieces) piecessum
FROM orders
GROUP BY year, month
PHP - Sum multiple array column individually
array_sum
is the right approach, but you'll first need to transpose your two arrays (that is, convert the rows into columns). The easiest way to do this in PHP is to pass null as the first parameter to array_map
:
$input = array_map(null, $arr, $arr2);
(This behaviour is described in the PHP docs here: https://www.php.net/manual/en/function.array-map.php)
Once your array is transposed, you can map array_sum
to each row to achieve your desired result:
print_r(array_map('array_sum', $input));
Array
(
[0] => 20
[1] => 40
[2] => 60
[3] => 80
)
See https://3v4l.org/AJtZa for a full example
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 Check If MySQL Results Returned Empty in PHP
Cron Job to Delete Files Created Before a Specific Time
Sending Post Requests Without Waiting for Response
Increase a Value Like = +1 in MySQL and PHP
How to Store .Pdf Files into MySQL as Blobs Using PHP
Group Array Data on One Column and Sum Data from Another Column
Mongodb Updating Fields in Nested Array
PHP Parse/Syntax Errors; and How to Solve Them
How to Implement Basic "Long Polling"
Generate an N-Digit Random Number
How to Pass a PHP Variable Using the Url
How to Do File Upload Using Jquery Serialization
Dompdf Not Rendering Images from the Server But Is Rendering from External Source
Nginx Error Recv() Failed (104: Connection Reset by Peer)