Suppose I have table of values:
+----+-------+
| Id | Value |
+----+-------+
| 1 | 2 |
| 2 | 9 |
| 3 | 5 |
| 4 | 5 |
| 5 | 8 |
| 6 | 1 |
+----+-------+
How can I calculate the percentage of rows who's value is higher than a given threshold:
Eg, with a threshold of 'higher than 5', how could I query to get the following:
+-------+------------------+
| Total | Percent above 5 |
+-------+------------------+
| 6 | 33.33 |
+-------+------------------+
Use conditional aggregation:
select count(*),
avg( (value > 5)::int ) * 100 as percentage_above_5
from t;