CODEWITHSUNDEEP
mysqlselectcountsumrounding
PHPFever
PHPFever

Reputation: 5

Getting an Average from counting Rows in MySQL

I am trying to display an average (percentile) of good answers from a DB for users that have > 99 answers given, whereas in column 'answer': 1=good answer and -1=bad answer...

The problem by using SUM is that 1 +-1 = 0 so technically if someone has 5 good answers and 5 bad answers, the query below gives me 0% when I should be getting 50%

SELECT 
  user_id, 
  CONCAT(ROUND( (SUM( `answer` ) / COUNT( `answer` ))*100 ,2 ),'%')  AS totals
FROM myDB
WHERE answer <>0
GROUP BY user_id
HAVING COUNT( `answer` ) >99
ORDER BY totals DESC

How would I make this work?

Upvotes: 0

Views: 52

Answers (3)

spencer7593
spencer7593

Reputation: 108490

Just use an expression to replace the -1 value with a 0.

If "good" answer is always value of 1, and answer is non-null. then replace 

SUM(answer)

with something as simple as: 

SUM(answer=1)

A boolean expression in MySQL returns 1 if true, 0 if false, or NULL. We could use a more explicit expression:

SUM(IF(answer=1,1,0))

Upvotes: 0

user4019957
user4019957

Reputation:

If you always use the values 1, -1 for answer, then try this:

select user_id
     , CONCAT(
       ROUND(
         count(case when answer > 0 then answer end) / count(answer) * 100, 2
       ), '%') totals
  from your_table
 group by user_id
 having count(answer) > 99

Upvotes: 1

Horaciux
Horaciux

Reputation: 6477

Try this, I couldn't test it now.

SELECT 
  user_id, 
  CONCAT(
    ROUND(
      ( SUM(
        case when `answer`>0 then 1 else 0 end
        ) / COUNT( `answer` )
      ) * 100, 2
    ),'%'
  ) AS totals
FROM myDB
WHERE answer <>0
GROUP BY user_id
HAVING COUNT( `answer` ) >99
ORDER BY totals DESC

Upvotes: 2

Related Questions