Issue with mathematical calculation inside SQL Query

Question

Having an issue with the total count of column records with the group by query. my data should be shown like:

Call Status               Call Results  Percentage
Answering Machine         1             5.6%
DO NOT CALL               9             50.0%
Good Transfer             4             22.2%
Looking for Information   4             22.2%
(blank)                                 0.0%
Total Calls               18

I am using the following query to do my work. But yet i am taking hardcore "18" because the problem occurs here that how i can take whole column count with group by.

$result = mysqli_query($link, "SELECT vs.status_name,vl.call_date,SUM(vl.called_count) as Total,SUM(vl.called_count)/18*100 as percentage from vl_users vu,".$vicidial_log_table." vl,vicidial_list vi,vicidial_statuses vs where vl.call_date >= '2014-01-17 00:00:00' and vl.call_date <= '2016-10-17 23:59:59' group by vs.status_name limit 100000") or die(mysqli_error($link));

The number 18 above is the sum of the call result column.

It this MySQL or SQL Server? Please be more careful with your tags. — DavidG
– DavidG, Commented Oct 17, 2016 at 10:37
Possible duplicate of How do I add a total as the last row in my sql? — DavidG
– DavidG, Commented Oct 17, 2016 at 10:39
But Actually @DavidG i don't have ny null value in the table. — junkk rr
– junkk rr, Commented Oct 17, 2016 at 10:42

Richard Housham · Accepted Answer · 2016-10-17 11:50:20Z

1

Right 3 ways of getting round this issue.

I would do the sum query first and then just add the sum into the percentage calc.
Might take a bit longer to calculate - in place of the 18 do a subquery to return the sum.
Don't use mysql to calculate the percentage. Instead use php to sum the array and then calc the percentage in each row while it's displaying.

1 - would be my recommendation.

Edit Ok, given the complexity of the query it seems 3 is the answer. So how to do it.

$result = mysqli_query($link, "SELECT vs.status_name,vl.call_date,SUM(vl.called_count) as Total from vl_users vu,".$vicidial_log_table." vl,vicidial_list vi,vicidial_statuses vs where vl.call_date >= '2014-01-17 00:00:00' and vl.call_date <= '2016-10-17 23:59:59' group by vs.status_name limit 100000") or die(mysqli_error($link));

$all_results = mysqli_fetch_all($result);

//might be a quicker way but
$sum = 0;
foreach($all_results as $s) {
    $sum  = $sum + $s['Total'];
}


//now to print
echo '<table><tr> <td>Call Status</td><td>Call Results</td><td>Percentage</td></tr>';

foreach($all_results as $row) {

   echo '<tr>';
echo '<td>'.$row['status_name'].'</td>';
echo '<td>'.$row['Total'].'</td>';

$percentage = 0;
if($sum > 0 && $row['Total'] > 0){
   $percentage = ($row['Total']/$sum)*100;
}
echo '<td>'.$percentage.'%</td>';


   echo '</tr>';




}

echo '</table>';

I just add that this is how I would do it, seems ok. I guess test and see.

edited Oct 17, 2016 at 11:50

answered Oct 17, 2016 at 10:41

Richard Housham

1,6782 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Richard Housham Over a year ago

Why? Calculate the sum of the rows first and then plug that in. Why wouldn't it work?

junkk rr Over a year ago

How i can access the sum of column sum inside the same query. It will cause Error.

Thorsten Kettner Over a year ago

@junkk rr: Like so: select a, sum(b), (select sum(b) from t) as total from t group by a.

Thomas G Over a year ago

The 3 solutions are correct and I would go 1 as well. Just throw the first query that Thorsten linked. Store the result in a PHP variable. Then when you prepare the scond query (your initial query), just change the 18 with that variable. Easy.

Thorsten Kettner Over a year ago

Why do you all prefer solution 1? Is MySQL's optimizer supposed to be so bad that it would run this non-correlated subquery multiple times? That is hard to imagine.

|

Collectives™ on Stack Overflow

Issue with mathematical calculation inside SQL Query

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related