Solved

# Get Total Amount of Added Numbers In Rows Between Multiple Tables

Posted on 2011-10-22
209 Views
Last Modified: 2012-06-27
Hi Experts,

Thanks for reading. I have two tables in my database: 1 is called Amounts, with AmountID and AmountWorth, and 2 is called MultipleAmounts, with MultipleAmountID, AmountID, Number. Each row in Amounts stores a number (the "worth"), for example:

AmountID: 1 (primary key, auto increment)
AmountWorth: 500 (actual amount)

Each row in MultipleAmounts tells how many multiple amounts there are in the Amounts table, for example:

MultipleAmountID: 1 (primary key, auto increment)
AmountID: 1 (which AmountID it belongs to in table Amounts)
Number: 5 (the number of the times the amount appears, AmountWorth multiplied by this number is the actual worth of that amount)

I hope I am clear. Can you please help advise or provide a quick script of how I can get the total amount of everything combined correctly? Thank you so much. It's late and my mind is mush, but I really want to get this configured. Any assistance is appreciated.
0
Question by:SevenAteAnthony
7 Comments

LVL 23

Expert Comment

What is the result of this query ? Is this you are looking for ?
``````SELECT A.AmountID, SUM (A.AmountWorth * M.Number)
FROM Amounts A
INNER JOIN MultipleAmounts M ON A.AmountID = M.AmountID
GROUP BY A.AmountID
``````
0

LVL 1

Author Comment

Hi RajkumarGS,

Does this work in a PHP script? I'm quite familiar with the MySQL coding you've placed. Logically it looks correct, though.

Thank you.
0

LVL 23

Expert Comment

Sorry. I am not familiar with PHP Script. I believe, this SQL Query would work in PHP.

Regards
Raj
0

LVL 11

Expert Comment

To use that script in php use something like

``````//  mysql connection ...

\$sql = "SELECT A.AmountID, SUM (A.AmountWorth * M.Number)
FROM Amounts A
INNER JOIN MultipleAmounts M ON A.AmountID = M.AmountID
GROUP BY A.AmountID";

\$result = mysql_query(\$sql);

if (!\$result)
{
echo "An error occured with the query : " . mysql_error();
}else{
while (\$row = mysql_fetch_array(\$result)
{

// The following will do a raw dump of each row produced with the query
echo "<pre>";
print_r(\$result);
echo "</pre>";
}
}
``````
0

LVL 1

Author Comment

Hi maeltar,

Thanks for your script. Basically, I'm just looking for 1 number to output. Is that possible?

Thank you!
0

LVL 11

Accepted Solution

try :

``````//  mysql connection ...

\$sql = "SELECT A.AmountID, SUM (A.AmountWorth * M.Number)
FROM Amounts A
INNER JOIN MultipleAmounts M ON A.AmountID = M.AmountID
GROUP BY A.AmountID";

\$result = mysql_query(\$sql);

if (!\$result)
{
echo "An error occured with the query : " . mysql_error();
}else{
\$row = mysql_fetch_row(\$result)
echo "<pre>";
print_r(\$result);
echo "</pre>";

}
``````
0

LVL 1

Author Closing Comment

Thank you!
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

#### 754 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!