GROUP BY statement Usage detailed

Use the WHERE clause with the GROUP BY clause

A grouped query can have a standard WHERE clause that eliminates unqualified rows before forming groups and evaluating column functions. The WHERE clause must be specified before the GROUP BY clause

$result = MySQL Tutorial _query ("Select Groups tutorial, name,goods from table group by Groups,name ORDER by name");
$arr = Array ();
$i = 0;
while ($row = Mysql_fetch_array ($result)) {
$arr [] = $row;
}

$m = $i-1;
foreach ($arr as $k => $v) {
if ($v [$i] [' groups '] <> $v [$m] [' groups ']) {
echo ' Split symbol ';
}
echo $v [' name '], $v [' goods '];
}

Look at an application example

The group by syntax allows you to group the query results according to each member of a given data column, resulting in a grouped summary table.

The column names in the SELECT clause must be grouped columns or column functions. The column function returns a result for each group defined by the groups by clause.

The structure and data of an employee information table are as follows:

id  name  dept  salary  edlevel  hiredate 
       1 Three Development Department 3 2009-10-11
      2 Li si Development Department 2500 3 2009-10-01
&NBSP;&NBSP;&NBSP;&N bsp;  3 Harry Design Department 2600 5 2010-10-02
      4 Wang Liu Design Department 2300 4 2010-10-03
  &nbs p;   5 MA VII Design Department 2100 4 2010-10-06
      6 Zhao Eight sales 3000 5 2010-10-05
       7 Nine Sales Department 3100 7 2010-10-07
      8 Sun 10 Sales 3500 7 2010-10-06

For example, I To list the results of the highest salary for each department, the SQL statement is as follows:

Select dept, Max (salary) as maximum
from staff
Group by Dept

Query results are as follows:
      dept  maximum 
      Development Department 2500
       Design Department 2600
      sales 3500

Let's go back to the function. Remember that we use SUM to calculate all sales (turnover)! What if our demand turns out to be the turnover (sales) of each store (store_name)? In this case, we have to do two things: first, we have to store_name and sales of the two fields are to be elected. Second, we need to make sure that all sales are separated according to each store_name. This syntax is:

　　

Select "Field 1", Sum ("Field 2") from "table name" Group By "Field 1"

Select Store_name, SUM (sales) from Store_information GROUP by Store_name

In our demonstration,

Store_information table

Store_name Sales Date

Los Angeles $1500 jan-05-1999

San Diego $ jan-07-1999

Los Angeles $300 jan-08-1999

Boston $700 jan-08-1999

We'll break into
Select Store_name, SUM (sales) from Store_information GROUP by Store_name

Results:

Store_name sum (Sales)

LOS Angeles $1800

San Diego $

Boston $700