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

Combine avg() and if() functions "Avg(if())" in mySQL

I have the below table:

s.userid  c.userid     c.action
3            3          timeout
3            3          timeout
7            7         confirmed
7            7         confirmed
7            7         confirmed
2            2          timeout
2            2         confirmed

For a given s.user_id, I am trying to calculate
count(confirmed)/ (count(confirmed) + count(timeout))

So the end result should look like:

s.userid  rate
3           0
7           1
2          0.5

I saw a solution where the following query was used:
select s.user_id, avg(if(c.action = ‘confirmed’, 1, 0)) as rate from tablename group by s.user_id;

I understand that the avg() function takes an expression and the if() function returns values based on a condition. I don’t understand how this query works internally. If the if() function returns a value and the avg() function is expecting a numeric column, then what is the logical sequence of operations in this query?

Tags:

2

Answers


  1. 0

    Using conditional aggregation we can try:

    SELECT
    user_id,
    SUM(action = 'confirmed') / SUM(action IN ('confirmed', 'timeout')) AS ratio
FROM yourTable
GROUP BY user_id
ORDER BY user_id;

    If a given user might have neither confirmed nor timeout actions, then the above would give a divide by zero. In that case, assuming you would want to report the ratio as zero, we can use case logic:

    SELECT
    user_id,
    CASE WHEN SUM(action IN ('confirmed', 'timeout')) > 0
         THEN SUM(action = 'confirmed') / SUM(action IN ('confirmed', 'timeout'))
         ELSE 0
    END AS ratio
FROM yourTable
GROUP BY user_id
ORDER BY user_id;
    Login or Signup to reply.
  2. 0

    This is a way to do it using LEFT JOIN :

    SELECT t.user_id, COALESCE(SUM(t2.user_id) / SUM(t.user_id), 0) AS rate
FROM mytable t
LEFT JOIN mytable t2 ON t.user_id = t2.user_id 
                     AND t.action = t2.action 
                     AND t2.action = 'confirmed'
GROUP BY t.user_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search