CODEWITHSUNDEEP
phpmysql
CyberFla
CyberFla

Reputation: 71

mysql Sum() and percentage from the total sum

I have a query running fine with results how ever I want to be able to calculate the sum of the votes and get the percentage. I guess I have to get the total votes before I can calculate the percentage. Following is part of my code. 

    <?php
    $query = "SELECT candidates.CandidateName, candidates.CandidateVotes, candidates.Party, mainrace.MainRaceName, race.RaceName
    FROM candidates
    JOIN race ON race.RaceID = candidates.RaceID
    JOIN mainrace ON mainrace.MainID = candidates.MainID
    WHERE candidates.RaceID = 9";

    $result = mysql_query($query) or die(mysql_error());

    //Loop through db fields. 

    for($i=0; $row = mysql_fetch_array($result); $i++){
    ?>


    <tr>
           <td>Perentage</td>
           <td><?php echo $row['CandidateName']; ?></td>
           <td><?php echo $row['CandidateVotes']; ?></td>

           </tr>
       <?php
            }
       ?>
       <tr>
         <td></td>
         <td>Total Votes:</td>
         <td>Sum totals</td>

       </tr>

At this point I want to be able to run query before this other query and get the 

query = "SELECT sum(candidates.CandidateName) FROM candidates WHERE candidates.RaceID =9";

Then I can calculate $Percentage= $CadidateVotes/100;

enter image description here

I appreciate your time and I hope that you have an answer for me. Sincerely

Upvotes: 0

Views: 472

Answers (1)

yk11
yk11

Reputation: 768

You have several options.

First, you could run a query to get the total number of votes, so then you can divide the candidate's votes by it to get the percentage as you process the query results.

Or you could first process the query results into an array while also adding up total votes, and then process the array. This option requires a bit more code, but should be faster as you do not need to query the database twice.

$candidates=[];
$total_votes=0;
for($i=0; $row = mysql_fetch_array($result); $i++) {
    $candidates[$row['name']]['votes'] = $row['votes'];
    $total_votes += $row['votes'];
}

foreach ($candidates as $name => $votes) {
$percent = $votes / $total_votes;
?>

Upvotes: 1

Related Questions