We help IT Professionals succeed at work.

Return % of returns with value

SheppardDigital
on
155 Views
Last Modified: 2014-08-21
Hi,

I have a table with the following fields;

id
player_id = int
fixture_id = int
up_vote = bool

I'm trying to come up with a query where I can return the % of results where the up_vote is equal to 1

so if there are 100 rows for fixture_id 78, and the value of up_vote in 75 of those rows in 1, then the result would be 75%.

I don't really know where to start with this one without performing multiple queries which I'm trying to avoid if I can.
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
You might be able to get this with a subquery, but if you have a scripting language available it might be easier to get good results with multiple queries, and I doubt there would be a noticeable difference in performance.  Do you have PHP available?

Author

Commented:
Yes, PHP is available and it's how I'm currently using it as my current solution.

The query returns all results for a given fixture_id, and then using PHP I'm counting the total results and looping through the results and counting all of those rows where up_vote = 1, then working out the percentage from there.

I guess I thought it may have been better doing it within the query, but maybe not.
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Footnote about an assumption.  I don't think MySQL has a "boolean" data type and the common literature I've read indicates that TINYINT is about as close as you can get.  So I defined the column that way.  However TINYINT is also capable of carrying non-zero values greater than one (Don't know if negative values will work). So some care might be needed to be sure that a SUM() on a TINYINT column produced an accurate answer.  Just a thought.

Author

Commented:
Thanks, that does exactly what I require.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Thanks for the points -- it's a great question! ~Ray

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.