One column displays only one field, the rest calculate from all applicable records

Question

Back again I'm afraid!

I have this SQL Query - populating a table.

echo SQLResultTable("SELECT SalesExec, COUNT(SalesExec)AS 'Total<br/> Checks', $AverageScore, `SUM(Autofails) AS 'Total <br/>AutoFails', $AverageAutofails FROM Data_Table WHERE BranchManager = '$BM'");`

In the database, I currently have 3 records. 2 for Sales Exec A, 1 for Sales Exec2.

The output form the above, is producing one row in a table. with just Sales Exec1's name. However all the other fields are adding up / averaging etc, all 3 records values.

How do I get the table to produce the proper 2 rows I need with the averages/counts/sums etc just applying to the record that relates to that Sales Exec?

To get the table working, I snagged this function from a different site, that seems to work lovely other than this issue.

function SQLResultTable($Query)
{
    $host = "localhost";
$user = "root";
$pass = "";
$db = "Quality_Monitoring";
    $link = mysql_connect($host, $user, $pass) or die('Could not connect: ' . mysql_error());      //build MySQL Link
    mysql_select_db($db) or die('Could not select database');        //select database
    $Table = "";  //initialize table variable

    $Table.= "<table border='1' style=\"border-collapse: collapse; text-align: center; font-size: small; cellspacing: 5px; \">"; //Open HTML Table

    $Result = mysql_query($Query); //Execute the query
    if(mysql_error())
    {
        $Table.= "<tr><td>MySQL ERROR: " . mysql_error() . "</td></tr>";
    }
    else
    {
        //Header Row with Field Names
        $NumFields = mysql_num_fields($Result);
        $Table.= "<tr style=\"background-color: #000066; text-align: center; color: #FFFFFF;\">";
        for ($i=0; $i < $NumFields; $i++)
        {     
            $Table.= "<th>" . mysql_field_name($Result, $i) . "</th>"; 
        }
        $Table.= "</tr>";

        //Loop thru results
        $RowCt = 0; //Row Counter
        while($Row = mysql_fetch_assoc($Result))
        {
            //Alternate colors for rows
            if($RowCt++ % 2 == 0) $Style = "background-color: #00CCCC;";
            else $Style = "background-color: #CCCCCC;";

            $Table.= "<tr style=\"$Style\">";
            //Loop thru each field
            foreach($Row as $field => $value)
            {
                $Table.= "<td>$value</td>";
            }
            $Table.= "</tr>";
        }
       // $Table.= "<tr style=\"background-color: #000066; color: #FFFFFF;\"><td colspan='$NumFields'>Query Returned " . mysql_num_rows($Result) . " records</td></tr>";
    }
    $Table.= "</table>";

    return $Table;

}

?>

Any help appreciated, cheers!

You should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). — Matt, Aug 07 '12 at 13:02

score 1 · Accepted Answer · answered Aug 07 '12 at 13:03

The SQL statement makes use of aggregate functions COUNT(), SUM() but has no GROUP BY. GROUP BY SalesExec. Because MySQL is lenient about the presence and contents of the GROUP BY clause, it will return one row, probably with the first SalesExec, but with aggregates calculated over all SalesExec, which is not a meaningful result.

echo SQLResultTable("
  SELECT SalesExec, COUNT(SalesExec)AS 'Total<br/> Checks', $AverageScore, `SUM(Autofails) AS 'Total <br/>AutoFails', $AverageAutofails 
  FROM Data_Table 
  WHERE BranchManager = '$BM' 
  GROUP BY SalesExec
");`

PERFECT. Thankyou, exactly how you described it. Many Thanks. — Chris Spalton, Aug 07 '12 at 13:08

One column displays only one field, the rest calculate from all applicable records

1 Answers1