How can I get maximum value from query?

Question

I'm using PHP and MySQL, and I'm looking for a select query to get the maximum and value from the result of the query. I have this table for users (id, university, score). I have tried:

<?php
$connect = mysqli_connect('localhost', 'root', '', 'test') or die ( mysqli_error($connect)); 

$output = '';


$search = mysqli_real_escape_string($connect, $_POST["query"]);
$query = "select t.uni, 
(select  count(*) from users where `score` =8 and `uni` = t.uni)*8 as 'rscore',
 (select  count(*) from users where `uni` = t.uni) as 'total'
from users t group by t.uni
";

$result = mysqli_query($connect, $query);
if (mysqli_num_rows($result) > 0) {

    $output .= '
 ';
    $i = 1;
    while ($row = mysqli_fetch_array($result)) {
        echo '
   <tr>
    <td align="center">' . $i . '</td> 
    <td width="10%">' . $row["uni"] . '</td>
    <td align="center">' . $row["rscore"] . '</td>
    <td align="center">' . $row["total"] . '</td>
   </tr>
  ';
        $i++; 
    }
} 
?>

I would like to know how to select the max value for score column and write it in a new column. The result I want like this:

The code shows no attempt to do this at all. VTC as too broad. It's a simple matter of putting records into an array and getting the max, http://php.net/max, or asking the database to do it, https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column.html — miken32, Apr 05 '19 at 18:42
Also, you don't need those subqueries. Replace the first with `SUM(score=8)*8` and the second is simply `COUNT(*)` — Patrick Q, Apr 05 '19 at 18:51

score -1 · Answer 1 · answered Apr 05 '19 at 18:45

Your query can be:

select t.uni, 
(select count(*) from users where `score` = 8 and `uni` = t.uni) * 8 as 'rscore',
(select count(*) from users where `uni` = t.uni) as 'total'
(select max(score) from users) as 'Max_Score'
from users t
group by t.uni

And where you echo the table rows, just add the Max_Score column:

echo '
   <tr>
    <td align="center">' . $i . '</td> 
    <td width="10%">'.$row["uni"].'</td>
    <td align="center">'.$row["rscore"].'</td>
    <td align="center">'.$row["total"].'</td>
    <td align="center">'.$row["Max_Score"].'</td>
   </tr>
  ';

max value comes from (rscore) query results, not from the score column in the data table. @Patrick B — المشرف العالي, Apr 05 '19 at 19:01

How can I get maximum value from query?

1 Answers1