How to calculate percentage in group by with condition?

I have a table with this schema:

+---------+-------------+-----------+---------+
|   id    |   provider  |  status   | message |
+---------------------------------------------+
|    1    |   Google    |  success  |  ...    |
|    2    |   Facebook  |  failure  |  ...    |
|    3    |   Facebook  |  failure  |  ...    |
|    4    |   Google    |  failure  |  ...    |
+---------------------------------------------+

I need to know percentage of failure for example:

google -> 50% failure

Facebook -> 100% failure

I use mysql 5.7

I tried to reach this destination using group by, but I couldn’t

Answer

You can do conditional aggregation. I like to do this with avg():

select provider, avg(status = 'failure') failure_ratio
from mytable
group by provider

For each provider, this gives you a numeric value between 0 and 1 that represents the ratio of records in 'failure' status. You can multiply that by 100 if you want a percentage instead.