Mysql – Calculate new percentage when new record is added

MySQLPHP

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

Best Answer

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;
    }
}
Related Question