CODEWITHSUNDEEP
mysqlgroup-byaverageconditional-aggregation
jackomelly
jackomelly

Reputation: 543

MySQL count percentual of occurrences greater than

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.

Upvotes: 1

Views: 45

Answers (2)

forpas
forpas

Reputation: 164064

Use AVG() aggregate function with conditional aggregation:

SELECT person,
       100 * AVG(amount > ?) percent
FROM tablename
GROUP BY person;

See the demo.

Upvotes: 1

Akina
Akina

Reputation: 42612

SELECT person, 100 * SUM(amount > @criteria) / COUNT(*) needed_percent
FROM src_table
GROUP BY person

where @criteria is needed amount value (3, 5, etc.).

Upvotes: 2

Related Questions