Finding percentage of users with certain conditions

November 12, 2022

Using MySQL, I need to find the percentage of users with >= 30 AverageActiveMinutes AND the percentage of users with < 30 AverageActiveMinutes from a table with the following structure:

Id	AverageActiveMinutes
1503960366	37.92
1644430081	2.50
1844505072	0.00
3977333714	19.82
5553957443	97.88
7086361926	47.46
8792009665	1.67

I am looking for an output with two columns and one row that looks something like this:

PercentOver30	PercentUnder30
42.9	57.1

>Solution :

You can use aggregation. In MySQL:

select avg(AverageActiveMinutes > 30) RatioOver30,
   1 - avg(AverageActiveMinutes > 30) RatioUnder30
from mytable

Here, MySQL evaluates the predicates once for each row; when the condition is met, it is intepreted as 1 by avg() (and conversely 0 when the condition is not met). The average of that gives you the ratio of rows that satisfy the predicate. You can multiply it by 100 if you prefer a percentage.