Add Up Column Total

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Add Up Column Total

Post 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"] . "'");
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

I am still at a loss.

Where in that link does it show me what I am looking for?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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."
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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"] . "');
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post 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"];
Post Reply