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:

Code: Select all

SELECT VERSION();
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"];