I'm trying to calculate a win chance from buying a freebies ticket in the percentage of each user that has been recorded in the database

here is my freebies table

fr_id | user_id
1     | 60
2     | 50
3     | 4
4     | 60
5     | 60
6     | 60
7     | 4
8     | 60
9     | 50
10    | 50

the total number of tickets is 10

• user id 60 have a 5 tickets
• user id 50 have a 3 tickets
• user 4 have 2 tickets

so I want to calculate on each user how much win chance they get

for example :

• user 60: 70% win chance
• user 50: 25% win chance
• user 4: 5% win chance

how to calculate it in PHP code based on user id?

$win_chance = $database->query("SELECT * FROM freebies WHERE user_id='60'");
echo $win_chance; //example 70%

CodePudding user response：

You can use next simple query:

select 
    sum(user_id = 60) / count(*) as chance
from tickets;

test SQL query

Below is PHP implementation:

<?php

$user_id = 60;

$query = "select 
    sum(user_id = ?) / count(*) as chance
from tickets;";

// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute([$user_id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
printf('Win chance for user %d is %s ' . PHP_EOL, $user_id, $row['chance']);

PHP test

CodePudding user response：

The below should give you the percentage rate of each user

with cte as (
select 1 as fr_id, 60 as user_id
union all select 2, 50
union all select 3, 4
union all select 4, 60
union all select 5, 60
union all select 6, 60
union all select 7, 4
union all select 8, 60
union all select 9, 50
union all select 10, 50)

select user_id, 
       cast(count(fr_id) as float)/(select count(*) from cte) * 100 
from cte 
group by user_id;