Search code examples
mysqlsqlaggregate-functionspercentage

Finding percentage of users with certain conditions

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.