1

I have this MySQL that I am using to calculate a percentage for a segment in a call. When a new record is added, I am able to get the correct percentage for each row.

$sumGreeting = $dbcon->prepare("
    SELECT  SUM(greeting_msg + provide_agent_name + clear_and_aud +
                confirm_caller_name + confirm_caller_tel +
                ask_alt_number) AS SumGrt
    FROM  table
    GROUP BY  coach , id

"); $sumGreeting->execute();

I want to calculate the final percentage each time a new record is added. Taking in mind the previous or existing records. At the moment I'm getting this result below:

enter image description here

I'm using this code:

    $initRows = 0;
    $result = 0;
    while ($row = $sumGreeting->fetch(PDO::FETCH_ASSOC))
    {
         $initRows = $row['SumGrt'];
         $result = ($initRows/12)*100;
     echo var_dump($result);
    echo $result ." <br />";    
    }

The Expected results will be the final percentage of all the columns.

This section has a total of 12 points.

Thanks in advance

1
  • Either drop the GROUP BY or add coach, id to the SELECT list.
    – Rick James
    Jun 22 '18 at 20:06
1

Why don't you sum the result for each group and use that sum to calculate the overall percentage? E.g. You have 4 groups with result 34,23,56,78. The total percent can be calculated by: total = sum(34,23,56,78)/4 * 100;

After running the aforementioned php code on insertion, use the value of the $result variable to perform the calculation.

Wanted to put as a comment but I can't... I will try to put the above in code. Does this help?

This is a sample:

$conn = mysqli_connect("localhost","root","","test"); # replace the details as it becomes necessary
if(!$conn){
die("Connection failed: " . mysqli_connect_error());
}
else{
    $initRows = 0;
    $result = 0;
    $sql = "SELECT SUM(num1 + num2 + num3) AS SumNumbers  FROM numbers GROUP BY numid";
    $res = $conn->query($sql);
    $initRows = 0;
    $rowNum = 0;
    $result = 0;
    $total = 0;
    if ($res->num_rows > 0) {
       while ($row = $res->fetch_assoc())
        {
            $initRows = $row['SumNumbers'];
            $rowNum = $res->num_rows;     #this will give the number of rows
            $columncount = mysqli_num_fields($res); # this will give the total number of columns
            $result = floor(($initRows/$columncount)*100); #this will give the percentage 
            $total += $result;     #this will give a total of all results but not the percentage. if you need the persentage of the total, you can now derive it using the $total variable
            echo $result ." <br />";
        }
        echo "Total is " . $total . " <br/>";
        $totalres = floor(($total/$rowNum)*100);
        echo "Total result percentage is " . $totalres;
    }
}
3
  • Hi Shully. I tried the above logic, but I am still a bit lost. Please clarify a bit more?
    – Pulane
    Jun 22 '18 at 11:17
  • I was trying to give a sample code but I don't use PDO... Can I use MySQLi syntax, will that be okay for you?
    – Shully
    Jun 22 '18 at 11:23
  • I have edited my answer and added a sample code... See if it makes sense to you and if it solves the problem. @pulaS29
    – Shully
    Jun 22 '18 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.