Question 224930 in Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

2

Answers


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

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

    Login or Signup to reply.
  2. 0

    Use AVG() aggregate function with conditional aggregation:

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

    See the demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search