How to calculate the percentage in eloquent?
Solution 1
If you want a pure Eloquent solution, do this:
$wl = Member::whereIn('result', ['W', 'L'])->count();
$total = Member::count();
$percent = $wl / $total * 100;
This will be much more efficient than actually retrieving any records from DB.
Solution 2
Your question is a bit vague, but from what I see the COUNT
function should be sufficient here:
SELECT
100.0 * COUNT(W) / COUNT(*) AS w_pct,
100.0 * COUNT(L) / COUNT(*) AS l_pct
FROM yourTable;
Of course, if you are already doing an aggregation then we can modify the above query to include GROUP BY
.
Solution 3
And what if the $total=0? In this scenario dividing by zero will give an error. Make sure to check it as well like:
if ($total != 0) {
$percent = $wl / $total * 100;
} else {
$percent = 0;
}
I hope you will find it helpful in the future.
Hkm Sadek
I am currently for Hombolt Technology, a software firm based on USA.
Updated on June 27, 2022Comments
-
Hkm Sadek almost 2 years
please have a look at the problem. You will find it interesting. I have a table
member
in which I have a columnresult
. In this result column three values can be possible and at one time only one value can be used. The value that are possibleW
,L
andV
. Forget about theV
as it is not required here.Let's say, I have 5 rows and in 3 rows I have
W
and in 1 row I haveL
and last one isV
.So here 3W and 1L out of 4(W+L together). So the percentage of W is 75%. How can I calculate it using mysql and if it is possible using eloquent would be better.
Currently, I am using this solution 1. Getting all the rows 2. Running php loop 3. And based on the value of the result column I am calculating the percentage. works fine. But I think if there are better solution why not to find it :)
Hope you have something for this problem. Thanks in advance.