Return % of returns with value

SheppardDigital
SheppardDigital used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2016

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
Top Expert 2016
Commented:
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

Become a Certified Penetration Testing Engineer

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.

Most Valuable Expert 2011
Top Expert 2016

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
Top Expert 2016

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today