CODEWITHSUNDEEP
phpmysql
Michael St Clair
Michael St Clair

Reputation: 6625

MySQL query using calculation within sum with field from different table

I have the following query that I need to use to sum a column in a table, but then I need to multiple it by CONTRACT_PERCENTAGE. CONTRACT_PERCENTAGE is stored in a different table than royalties, called isrc_codes. Both tables share the TRACK_ISRC column. Is there a way I can get the CONTRACT_PERCENTAGE from isrc_codes and use it to put in the sum calculation?

$add = mysqli_query($con,"SELECT sum(DISTRIBUTED_AMOUNT / EXCHANGE_RATE * CONTRACT_PERCENTAGE) FROM royalties WHERE BUNDLE_ARTIST = '".$row['artistname']."'");         
while($addamount = mysqli_fetch_array($add)) {              
echo '<td>$' . number_format($addamount[0], 2, '.', '') . '</td>';          
}

Upvotes: 0

Views: 169

Answers (1)

Charlie Martin
Charlie Martin

Reputation: 8406

Try this query..

"SELECT sum(r.DISTRIBUTED_AMOUNT / r.EXCHANGE_RATE * i.CONTRACT_PERCENTAGE) FROM royalties r, isrc_codes i WHERE r.TRACK_ISRC = i.TRACK_ISRC AND r.BUNDLE_ARTIST = '".$row['artistname']."'"

Upvotes: 1

Related Questions