Hi,
What is the best way to add up all of the dollar amounts (trans_amount) in a database table for each customer (cust_id) and display the results? Each customer will have multiple transactions.
Table Structure
trans_id - cust_id - trans_date - trans_amount
Thanks,
HedgeHogfor each cust_id?
just echo the trans_amount to teh screen. it cust_id unique? how is your table setup? what values are in trans_amount?
need more info.Hey Scoutt,
What I want to do is display the total sum of all transaction (trans_amount) for let say client number 230 (cust_id). Which based on the table below should equal $128.21 (10.56 + 52.65 + 65.00)
Each client can have an unlimited number of entries in the table and I want to total all of them.
here is an example of what the table would look like:
trans_id - cust_id - trans_date - trans_amount
1 - 230 - 10/20/2002 - 10.56
2 - 150 - 10/22/2002 - 20.36
3 - 230 - 11/23/2002 - 52.65
4 - 180 - 11/25/2002 - 12.99
5 - 230 - 12/30/2002 - 65.00
I hope this explains it better.
HedgeHogoh yes that will work.
so we will let mysql work for us and do that math
select sum(trans_amount) AS amount, cust_id from clients where cust_id = '230'
try that.Once again.....Thank you Scoutt.
Exactly what I was looking for.
HedgeHogIf you want to add up the trans_amount for all the customers at once, and display it like a list, this would accomplish it, just if you'd need it sometime
$query = "SELECT cust_id, SUM(trans_amount) AS total FROM clients GROUP BY cust_id;";
$result = mysql_query($query, $link);
while($client = mysql_fetch_array($result)) {
echo $client["cust_id"]." - ".$client["total"]."<BR>";
}
and if you'd like to order them by say, descending total transfer amounts, you can just change the query to:
$query = "SELECT cust_id, SUM(trans_amount) AS total FROM clients GROUP BY cust_id ORDER BY total DESC;";
What is the best way to add up all of the dollar amounts (trans_amount) in a database table for each customer (cust_id) and display the results? Each customer will have multiple transactions.
Table Structure
trans_id - cust_id - trans_date - trans_amount
Thanks,
HedgeHogfor each cust_id?
just echo the trans_amount to teh screen. it cust_id unique? how is your table setup? what values are in trans_amount?
need more info.Hey Scoutt,
What I want to do is display the total sum of all transaction (trans_amount) for let say client number 230 (cust_id). Which based on the table below should equal $128.21 (10.56 + 52.65 + 65.00)
Each client can have an unlimited number of entries in the table and I want to total all of them.
here is an example of what the table would look like:
trans_id - cust_id - trans_date - trans_amount
1 - 230 - 10/20/2002 - 10.56
2 - 150 - 10/22/2002 - 20.36
3 - 230 - 11/23/2002 - 52.65
4 - 180 - 11/25/2002 - 12.99
5 - 230 - 12/30/2002 - 65.00
I hope this explains it better.
HedgeHogoh yes that will work.
so we will let mysql work for us and do that math
select sum(trans_amount) AS amount, cust_id from clients where cust_id = '230'
try that.Once again.....Thank you Scoutt.
Exactly what I was looking for.
HedgeHogIf you want to add up the trans_amount for all the customers at once, and display it like a list, this would accomplish it, just if you'd need it sometime
$query = "SELECT cust_id, SUM(trans_amount) AS total FROM clients GROUP BY cust_id;";
$result = mysql_query($query, $link);
while($client = mysql_fetch_array($result)) {
echo $client["cust_id"]." - ".$client["total"]."<BR>";
}
and if you'd like to order them by say, descending total transfer amounts, you can just change the query to:
$query = "SELECT cust_id, SUM(trans_amount) AS total FROM clients GROUP BY cust_id ORDER BY total DESC;";