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 |
CodePudding user response:
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.