[SOLVED] how to calculate percentage in sql?

[nashsaint]

Problem: calculate percentage in one sql query.

 

Table: logs_tbl

 

fields:

fname = varchar

lresult = varchar (either "good" or "bad" entries)

 

I tried making query but my knowledge is not great so i failed.

this is the formula i reckon is gonna be like but couldn't

translate it into sql query:

 

(lresult with "good" entries) / (all lresult entries) * 100

grouped by fname.

 

I only want the percentage for "good" entries.

 

any help is greatly appreciated. thanks.

[RichardRotterdam]

you can use an alias to show the percentage. how do you separate good entries for bad entries and what does your query look like so far?

[nashsaint]

you're right.  real problem is how to separate the "good" from "bad" and then divide the "good" from over all total.

 

I tried this:

SELECT count(lresult) / count(lresult) * 100, fname FROM logs_tbl WHERE lresult = 'good' GROUP by fname

 

but that mean only counting the ones with good result.

 

am thinking:

SELECT count(lresult="good") / count(lresult) * 100, fname FROM logs_tbl GROUP by fname

 

but i doubt it's going to work. 

[RichardRotterdam]

are you saying "lresult" is a field containing either "good" or "bad?"

You can use sub queries if thats the case

[nashsaint]

yes,

are you saying "lresult" is a field containing either "good" or "bad?"

You can use sub queries if thats the case

.. yes.

 

can you please show me a sample query? thanks.

[kickstart]

Hi

 

Crude but does work:-

 

SELECT a.fname, (GoodResult / (GoodResult  + BadResult)) * 100

FROM (SELECT fname, count(*) GoodResult FROM logs_tbl WHERE lresult = "good" GROUP BY fname) a

INNER JOIN (SELECT fname, count(*) BadResult FROM logs_tbl WHERE lresult = "bad" GROUP BY fname) b

ON a.fname = b.fname

 

All the best

 

Keith

[nashsaint]

thanks a lot Keith but I get a parse error?

[kickstart]

Hi

 

Exactly what is the error you are getting?

 

I have tried it on a test table in phpmyadmin and it seems to work fine.

 

All the best

 

Keith

[nashsaint]

Keith,

 

I tested it on phpadmin as well and it works fine but when i run the .php file i get this error:

 

Parse error: parse error in C:\wamp\www\percent.php on line 5

 

line 5 is the start of the query.

[kickstart]

Hi

 

Probably the double quotes around good and bad.

 

All the best

 

Keith

[nashsaint]

Hi Keith,

 

you're right. I changed the double with single quote and it works.  However, the output is only showing one record. In my phpadmin I have about a hundred.  Sorry for the trouble Keith, really don't know how to sort this out.

 

Thanks.

[kickstart]

Hi

 

Can you post the code that processes this.

 

All the best

 

Keith

[nashsaint]

Hi Keith,

 

sorry, It's working now.  I just mistyped the field name when i echo it.  I have one problem though.  It doesn't show the ones with 100% result.

 

(GoodResult / (GoodResult + BadResult)) * 100

 

The ones with 0 BadResult is not displayed in the output.

 

Thanks again Keith.  You've helped me alot already.

[nashsaint]

and this is the script.

 

<?php require_once('../Connections/dbcon.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

 

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

 

$maxRows_crPercent = 10;

$pageNum_crPercent = 0;

if (isset($_GET['pageNum_crPercent'])) {

  $pageNum_crPercent = $_GET['pageNum_crPercent'];

}

$startRow_crPercent = $pageNum_crPercent * $maxRows_crPercent;

 

mysql_select_db($database_dbcon, $dbcon);

$query_crPercent = "SELECT a.fname, (GoodResult / (GoodResult  + BadResult)) * 100 Percentage FROM (SELECT fname, count(*) GoodResult FROM logs WHERE lresult = 'Good' GROUP BY fname) a INNER JOIN (SELECT fname, count(*) BadResult FROM logs WHERE lresult = 'Bad' GROUP BY fname) b ON a.fname = b.fname ORDER BY Percentage DESC";

$query_limit_crPercent = sprintf("%s LIMIT %d, %d", $query_crPercent, $startRow_crPercent, $maxRows_crPercent);

$crPercent = mysql_query($query_limit_crPercent, $dbcon) or die(mysql_error());

$row_crPercent = mysql_fetch_assoc($crPercent);

 

if (isset($_GET['totalRows_crPercent'])) {

  $totalRows_crPercent = $_GET['totalRows_crPercent'];

} else {

  $all_crPercent = mysql_query($query_crPercent);

  $totalRows_crPercent = mysql_num_rows($all_crPercent);

}

$totalPages_crPercent = ceil($totalRows_crPercent/$maxRows_crPercent)-1;

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

</head>

 

<body>

<table border="0">

  <tr>

    <td>fname</td>

    <td>Percentage</td>

  </tr>

  <?php do { ?>

    <tr>

      <td><?php echo $row_crPercent['fname']; ?></td>

      <td><?php echo $row_crPercent['Percentage']; ?></td>

    </tr>

    <?php } while ($row_crPercent = mysql_fetch_assoc($crPercent)); ?>

</table>

</body>

</html>

<?php

mysql_free_result($crPercent);

?>

[kickstart]

Hi

 

Mmm, you are right. Time to look at this. Think what I had missed is that the subselects won't return anything if the count is 0.

 

All the best

 

Keith

[kickstart]

Hi

 

This SQL will do it. However I am not happy with how convoluted it is and I must have missed something obvious.

 

SELECT a.fname, (GoodResult / (GoodResult  + BadResult)) * 100 Percentage 
FROM (SELECT y.fname, count(z.fname) GoodResult
FROM (SELECT DISTINCT fname from logs_table) y LEFT OUTER JOIN logs_table z 
ON  y.fname = z.fname 
AND z.lresult = 'good'
group by y.fname) a 
INNER JOIN (SELECT y.fname, count(z.fname) BadResult
FROM (SELECT DISTINCT fname from logs_table) y LEFT OUTER JOIN logs_table z 
ON  y.fname = z.fname 
AND z.lresult = 'bad'
group by y.fname) b 
ON a.fname = b.fname 
ORDER BY Percentage DESC

[nashsaint]

hi keith,

 

It works!!!!

 

I am very greatful for this.  thanks a lot keith..