Return % of returns with value

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.
SheppardDigitalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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?
0
SheppardDigitalAuthor 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.
0
Ray PaseurCommented:
OTOH, this seems to work.  Please see line 90 for the query (the rest is just setup and proof of concept).

Link here: http://iconoun.com/demo/temp_shepparddigital.php
<?php // demo/temp_shepparddigital.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Database/MySQL/Q_28501440.html

// CREATE AN ARRAY OF TEST DATA
$test_arrays = array
( array( "fixture_id" => "1" , "up_vote" => "1" )
, array( "fixture_id" => "1" , "up_vote" => "1" )
, array( "fixture_id" => "1" , "up_vote" => "0" )
, array( "fixture_id" => "2" , "up_vote" => "1" )
, array( "fixture_id" => "2" , "up_vote" => "0" )
)
;

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id         INT     NOT NULL AUTO_INCREMENT PRIMARY KEY
, fixture_id INT     NOT NULL DEFAULT 0
, up_vote    TINYINT NOT NULL DEFAULT 0
)
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// LOADING OUR DATA INTO THE TABLE
foreach ($test_arrays as $array)
{
    $f = $array['fixture_id'];
    $u = $array['up_vote'];
    $sql = "INSERT INTO my_table ( fixture_id, up_vote ) VALUES ( '$f', '$u' )";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }
}

// QUERY WITH COMPUTATIONS
$sql
=
"
SELECT fixture_id, ups, tot, ups / tot as pct FROM
(
    SELECT fixture_id, COUNT(fixture_id) AS tot, SUM(up_vote) AS ups
    FROM my_table
    GROUP BY fixture_id
    ORDER BY fixture_id
) temp_table
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Ray PaseurCommented:
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.
0
SheppardDigitalAuthor Commented:
Thanks, that does exactly what I require.
0
Ray PaseurCommented:
Thanks for the points -- it's a great question! ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.