Performing arithmetic in SQL query

Question

So I'm working on a script that awards "trophies" to the top 4 performers of a game. The table logs each "grab" attempt, the user that performed it, and whether it was successful. I'd like to create a script that is able to pull the top four off of percentage of successful grabs (attempts / successes)

Is something like this possible within the query itself using mysqli?

I have successfully accomplished the code already by just looping through each table entry, but with thousands of attempts per month it just seems like a clunky way to go about it.

Here is an example of a row in the table, I am attempting to grab the top four based off of monthlyTries/monthlySuccessful

 id  userId   PetId   PalId  tries  successfulGrabs monthlyTries   MonthlySuccessful
 5   44550    84564   3967    825      268             120               37

Can you edit your question with a sample of the data please? — ash
– ash, Commented Aug 9, 2015 at 16:53
Thanks Miranda - FuzzyTree has sorted it for you though I believe :) — ash
– ash, Commented Aug 9, 2015 at 17:06

FuzzyTree · Accepted Answer · 2015-08-09 16:58:42Z

4

Assuming you have a success column that's either 1 or 0 you can sum the success and divide that by count(*) which is the total # of attempts

select user_id, sum(success)/count(*) percentage
from attempts a
group by user_id
order by percentage desc
limit 4

If the success column is not a 1/0 value you can use conditional aggregation

select user_id, sum(case when success = 'yes' then 1 else 0 end)/count(*) as percentage
from attempts a
group by user_id
order by percentage desc
limit 4

answered Aug 9, 2015 at 16:58

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-08-09 17:34:57Z

0

In MySQL, you can simplify the logic. If success takes on the values 0 and 1:

select a.user_id, avg(success) as percentage
from attempts a
group by a.user_id
order by percentage desc
limit 4;

Otherwise:

select a.user_id, avg(success = 'yes') as percentage
from attempts a
group by a.user_id
order by percentage desc
limit 4;

answered Aug 9, 2015 at 17:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Performing arithmetic in SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related