Page 1 of 1
Add Up Column Total
Posted: Thu Apr 06, 2006 12:08 pm
by icesolid
I have a database of records. Each record has a charge amount attached to it. I want to add the total amount for all of the charges in a database WHERE the customer = the customer selected. I was wondering if there was a MySQL built in ADD or COUNTING function that would add up all of the charge fields for the selection below?
My current selection code:
Code: Select all
mysql_query("SELECT * FROM cases WHERE customer='" . $_POST["customer"] . "'");
Example of my question (incorrect I am sure):
Code: Select all
mysql_query("SELECT * FROM cases ADD(charge) WHERE customer='" . $_POST["customer"] . "'");
Posted: Thu Apr 06, 2006 1:26 pm
by feyd
Posted: Thu Apr 06, 2006 2:01 pm
by icesolid
I am still at a loss.
Where in that link does it show me what I am looking for?
Posted: Thu Apr 06, 2006 2:03 pm
by feyd
Read through the functions there, in particular the one referenced in the hash of the link.. Here's a hint: it's one of the last functions listed and it's a another word for "add."
Posted: Thu Apr 06, 2006 3:09 pm
by icesolid
I tried using SUM but it is not working.
Here is my code:
Code: Select all
$total_amount = mysql_query("SELECT SUM(charge) FROM cases WHERE customer='" . $_POST["customer"] . "');
Posted: Thu Apr 06, 2006 3:37 pm
by feyd
How is it not working? Echo out the query string you are creating. Add
mysql_error() outputs to see if there's something you missed as well.
Posted: Thu Apr 06, 2006 3:57 pm
by icesolid
Here is my error:
Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/capous/public_html/executive.php on line 1584
Posted: Thu Apr 06, 2006 4:08 pm
by feyd
that doesn't relate to the query not working, but your MySQL connection not working or being malformed or some other such stuff.
Posted: Thu Apr 06, 2006 5:15 pm
by icesolid
Correction on my error:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(charge) WHERE customer='webdemo' AND date_completed BETWEEN
Posted: Thu Apr 06, 2006 6:55 pm
by RobertGonzalez
In your MySQL administration utility (phpMyAdmin, Query Browser, MySQL Administrator) run the following query to find out what version of MySQL you are running:
Then search the MySQL manual for that version and the SUM() function.
Posted: Fri Apr 07, 2006 9:41 am
by icesolid
I figured it out.
Here is my code:
Code: Select all
$result = mysql_query("SELECT SUM(charge) as total_amount FROM cases WHERE customer='" . $_POST["customer"] . "' AND date_completed BETWEEN '" . $_POST["start_date"] . "' AND '" . $_POST["end_date"] . "'");
$row = mysql_fetch_array($result);
echo $row["total_amount"];