MySQL聚合函数提供单个结果集中的所有数据。聚合函数将数据分组在一起。为此，可以使用select语句的两个新子句，“GROUPBY”和“having”子句。

MySQL Group by子句示例

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

  {

  die('无法连接mysql数据库: ' . mysql_error());

  }

mysql_select_db("mysql", $con);

print "简单查询";

$result = mysql_query("select * from emp_dtl");  //第一个查询

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Lastname</th>

<th>Role</th>

<th>Salary</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

   echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['Firstname'] . "</td>";

  echo "<td>" . $row['Lastname'] . "</td>";

  echo "<td>" . $row['role'] . "</td>";

    echo "<td>" . $row['salary'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

 

  //使用Group by子句
 

  print "<h2>MySQL: Group by子句</h2>";

$result = mysql_query("select id, avg(salary)as totalsal from emp_dtl group by id"); //第二次查询

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Salary</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

   echo "<tr>";

  echo "<td>" . $row['id'] . "</td>";

  echo "<td>" . $row['totalsal'] . "</td>";

  echo "</tr>";

  }

  echo "</table>";

  mysql_close($con);

?>

Group by子句

“group”子句用于基于一个或者多个列表达式对结果集的行进行分组。
GROUPBY子句确定所选行的分组方式。groupby子句跟在where子句后面。
如果需要，多个列或者表达式将包含在“groupby”子句中，并用逗号分隔。

语法

SELECT columns Name....... from table Name 
                    Where condition
                    Group by group_by_list
                    Having condition
                    Order by order_by_list