I have a table like this.
+ day + person + amount +
+------+---------+--------+
+ 1 + John + 4 +
+------+---------+--------+
+ 1 + Sam + 6 +
+------+---------+--------+
+ 2 + John + 3 +
+------+---------+--------+
+ 3 + John + 3 +
+------+---------+--------+
+ 3 + Sam + 5 +
+------+---------+--------+
+ 4 + John + 3 +
+------+---------+--------+
How can I get a percentual of entries where the amount is greater than 3 (or other) grouped by people? I want my output to be: John 25%, Sam 100%. When the clause is "amount greater than 5", I would have: John 0%, Sam 50%. It would be nice to minimize the overload when table becomes big.
Use AVG() aggregate function with conditional aggregation:
SELECT person,
100 * AVG(amount > ?) percent
FROM tablename
GROUP BY person;
See the demo.