How to do query and display the records with the sum of student money on every record?
Student ID Student Name Student Money
--------- ----------- --------------
1 John 100
2 Jenny 200
3 Ben 100
4 Andy 200
5 Lynna 100
Above is my table and i would like to retrieve record in this format:
Student ID Student Name Student Money totalCounting
--------- ----------- -------------- ---------
1 John 100 100
2 Jenny 200 300
3 Ben 100 400
4 Andy 200 600
5 Lynna 100 700
Select s.*, (SELECT SUM(s2.`Money`) from students as s2 WHEre s2.`ID` = s.`ID`) as 'totalCounting' from students AS s;
Something like this.
In php I would look to build an extra key in the array built from the query with a foreach loop. My SQL isn't strong enough to build it into the Query, so...
$total = 0;
foreach ($queryResults as $key=>$row){
$total = $total + $row['student_money'];
$queryResults[$key]['total_money'] = $total;
}
Then build the table with the $queryResults with the new column.