Percentage

[ianuk77]

Hi all,

I am new to php & mysql and could really do with some advice as I have been struggling with this problem all day.

I am trying to workout a percentage via a result from a mysql column. The column contains server 2003 EventID error codes which have been generated by the OS.

i.e:
576
528
540
538
529 ..etc

Now I have managed to calculate the total occurrence of an error code using the following php:

$query = "SELECT EventID, COUNT(EventID) AS tt FROM audit3
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo "<br />";
echo $row['EventID']," ". $row['tt'] ." ";
echo "<br />";
}
?>

I really want to work out the percentage of the specific error codes. I know I will need another query to work out the total then divide and multiply to get the percent. But I can’t seem to solve the problem any guidance would be greatly appreciated.

 

[towerbase]

One query will do:

SELECT EventID
     , COUNT(*) AS tt
     , ( SELECT COUNT(*) FROM audit3 ) AS tot
  FROM audit3
  GROUP BY EventID

You should then be able to process the result with something like:

...
  while ($row = mysql_fetch_array($result)) {
    $percent = ( $row['tt'] * 100 ) / $row['tot'];
    echo '<br />';
    echo $row['EventID'] . ' '. $percent . '%';
    echo '<br />';
  }

No doubt Rudy will come up with some SQL that will also calculate the percentages ...

Andrew
Hampshire, UK

 

[r937]

SELECT EventID
     , COUNT(*) AS tt
     , COUNT(*) * 100.0
       / ( SELECT COUNT(*) FROM audit3 ) AS pct
  FROM audit3
GROUP 
    BY EventID

;-)


r937.com | rudy.ca

 

[ianuk77]

Thanks for the reply’s both

Seem to be having trouble with both the queries though.

towerbase:
You have an error in your SQL syntax near 'COUNT(*) AS tt ( SELECT COUNT(*) FROM audit3 ) AS tot FROM audit3 GROUP BY Event' at line 1

r937:
ERROR 1064: You have an error in your SQL syntax near 'SELECT COUNT(*) FROM audit3 ) AS pct

Just running both from bash-mysql.

just to reiterate what im trying to do:

eventid occurrence percent
528 490 25.52
540 390 20.31

Ive managed to get it to work by doing:

get the total:

$query = "SELECT COUNT( EventID ) AS res1 FROM audit3";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
$total = $row['res1'];

percentage( EventID, $total );
}

then:

function percentage( $col, $total ) {

$query = "SELECT $col, COUNT($col) AS tt FROM audit3 GROUP BY $col ORDER BY tt DESC";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

$per = $row['tt'] / $total * 100;
printf ("%01.2f",$per);
echo "<br />";
echo $row['EventID']," ". $row['tt'] ." ";
echo "<br />";

}
}

I would much prefer to do this with one query though.

 

[r937]

you're on version 3.23 or 4.0, right?

r937.com | rudy.ca

 

[ianuk77]

UPDATE

r937's query works fine in access just not in mysql ?

 

[ianuk77]

@r937

+------------------+--------------------------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------------------------+
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (September 11, 2003) |
| protocol_version | 10 |
| version | 3.23.58 |
+------------------+--------------------------------------------------------------+

 

[guelphdad]

your version of mysql is severely out of date as you see. Time to upgrade.

 

[ianuk77]

Thank you all for the guidance, very appreciated

Ian