CODEWITHSUNDEEP
mysqlsql
Bigjo
Bigjo

Reputation: 633

How te get a percentage of rows in mysql

I have a table like this

user    result
john    +
mike    -
john    -
rita    +

I want to get the percentage of - grouped by user. So for my example the result must be:

user    %min
john    50%
mike    100%
rita    0%

Is that possible in mysql to create such a query?

Upvotes: 0

Views: 57

Answers (2)

marcothesane
marcothesane

Reputation: 6721

Gordon's answer implies a database platform that implicitly casts a Boolean TRUE to 1 and a Boolean FALSE to 0. Which is not prescribed by the standard. Should you run into an error going something like "Function avg(boolean) does not exist", try a CASE expression:

    WITH tb (usr,result) AS (
                  SELECT 'john','+'
        UNION ALL SELECT 'mike','-'
        UNION ALL SELECT 'john','-'
        UNION ALL SELECT 'rita','+'
    )
    SELECT 
      usr
    , AVG(CASE result WHEN '-' THEN 100 ELSE 0 END) AS percent_min
    FROM tb
    GROUP BY usr
    ORDER BY usr;

Happy playing -

Marco

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Just use conditional aggregation. Here is a simple method:

select user, avg(result = '-') as percent_min
from t
group by user;

This will give the result as a value between 0 and 1, which can then be formatted as you desire.

Upvotes: 2

Related Questions