CODEWITHSUNDEEP
mysqlsqlprobability
Onyr
Onyr

Reputation: 915

How to compute occurence probability of rows inside a SQL (MySQL) table using a single query?

I have a table containing some similar rows representing objects for a game. I use this table as a way to select objects randomly. Of course, I ignore the size of the table. My problem is that I would like to have a single query that returns the probability to select every object and I don't know how to proceed.

I can get the total number of objects I have in my table:

select count(id) from objects_desert_tb;

Which returns

+-----------+
| count(id) |
+-----------+
|        81 |
+-----------+
1 row in set (0.00 sec)

and I have a query that return the number of occurence of every object in the table:

select name, (count(name)) from objects_desert_tb group by name;

which gives:

+-------------------+---------------+
| name              | (count(name)) |
+-------------------+---------------+
| carrots           |             5 |
| metal_scraps      |            14 |
| plastic_tarpaulin |             8 |
| rocks_and_stones  |            30 |
| wood_scraps       |            24 |
+-------------------+---------------+
5 rows in set (0.00 sec)

Computing the probability for every object just consist in doing (count(name)) divided by the total number of rows in the table. For example with the row carrots, just compute 5/81, from the two queries given above. I would like a single query that would return:

+-------------------+---------------+
| carrots           |             5/81 = 0.06172839
| metal_scraps      |            0.1728...
| plastic_tarpaulin |            0.09876...
| rocks_and_stones  |            0.37...
| wood_scraps       |            0.29...
+-------------------+---------------+

Is there a way to use the size of the table as a variable inside a SQL query? Maybe by nesting several queries?

Upvotes: 0

Views: 527

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In MySQL 8+, you would just use window functions:

select name, count(*) as cnt,
       count(*) / sum(count(*)) over () as ratio
from objects_desert_tb 
group by name;

Upvotes: 1

forpas
forpas

Reputation: 164139

Cross join your queries:

select c.name, c.counter / t.total probability
from (
  select name, count(name) counter 
  from objects_desert_tb 
  group by name
) c cross join (
  select count(id) total 
  from objects_desert_tb 
) t

Upvotes: 1

Related Questions