• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Get Total Amount of Added Numbers In Rows Between Multiple Tables

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
SevenAteAnthony
Asked:
SevenAteAnthony
  • 3
  • 2
  • 2
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
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 

Open in new window

0
 
SevenAteAnthonyAuthor Commented:
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
 
Rajkumar GsSoftware EngineerCommented:
Sorry. I am not familiar with PHP Script. I believe, this SQL Query would work in PHP.

Regards
Raj
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
maeltarCommented:
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>";
  }
}

Open in new window

0
 
SevenAteAnthonyAuthor Commented:
Hi maeltar,

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

Thank you!
0
 
maeltarCommented:
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>";

}

Open in new window

0
 
SevenAteAnthonyAuthor Commented:
Thank you!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now