[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Return % of returns with value

Posted on 2014-08-20
6
Medium Priority
?
132 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.
0
Comment
Question by:SheppardDigital
  • 4
  • 2
6 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40273798
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
 

Author Comment

by:SheppardDigital
ID: 40273807
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
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40273876
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40274060
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
 

Author Closing Comment

by:SheppardDigital
ID: 40275687
Thanks, that does exactly what I require.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40276266
Thanks for the points -- it's a great question! ~Ray
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 2 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question