How to Calculate percentage in MySQL

Question

I have a database like this image,

id      q_id     body    user_id  thread
338222  433798  Testing    7178     1
338223  433798  Testing    5571     1
338224  433798  Testing    5571     1

And my query looks like this,

SELECT 
thread, 
user_id, 
COUNT(*) AS contribution FROM answers GROUP BY user_id, thread ORDER BY thread

Which will return thread, user_id and contribution. But I want to get the percentage of contribution. Let's say user_id 7178 contribute 1 times to thread 1 and thread 1 has 3 rows so the percentage will be 33.33%

thread  user_id contribution percentage
1       7178         1         33.33
1       5571         2         67.67 

How can I get that? Can anybody help me out?

Answer 1

I think that does the trick:

SELECT
  answers.thread,
  answers.user_id,
  COUNT(*) AS contribution,
  COUNT(*) / tmp.TOTAL * 100 AS percentage
FROM answers
  INNER JOIN (
    SELECT
      COUNT(*) AS TOTAL,
      thread
    FROM answers GROUP BY thread
  ) AS tmp ON tmp.thread = answers.thread
GROUP BY user_id, answers.thread;

Since you need two different aggregations (one for the total amount of contributions and one for the amount of contributions per user), you need a subquery.

Note that you need to make sure there are no 0 values in your table. If there are some 0s, you should add a safeguard for the division (with an IF THEN ELSE maybe?).