Group by statement usage

Use the where clause with the group by clause

Grouping query can have a standard where clause that eliminates undefined rows before forming a group and a computed column function. You must specify the where clause 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 'delimiter ';
}
Echo $ v ['name'], $ v ['goods '];
}

View Application Instances

The group by syntax performs grouping statistics on the query results based on each member of the given data column to obtain a grouping summary table.

The column name in the select clause must be a group column or column function. The column function returns a result for each group defined by the group by clause.

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

 

Id name dept salary edlevel hiredate
1 Zhang San Development Department 2000 3
2 Li Si Development Department 2500 3 2009-10-01
3 Wang Wu design department 2600 5 2010-10-02
4 Wang 6 Design Department 2300 4
Ma Qi Design Department 2100 4 2010-10-06
6 Zhao Ba sales department 3000 5
7 Qian JIU sales department 3100 7
8 sun ten sales department 3500 7

For example, 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

The query result is as follows:
Dept maximum
Development Department 2500
Design Department 2600
Sales 3500

Now let's go back to the function. Remember to use the sum command to calculate all sales (turnover! What should we do if we want to calculate the turnover (sales) of each store (store_name? In this case, we need to do two things: first, we need to select both store_name and sales columns. Second, we need to confirm that all sales are calculated separately based on each store_name. The syntax is:

　　

Select "column 1", sum ("column 2") from "table name" group by "column 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 $250 jan-07-1999

Los angeles $300 jan-08-1999

Boston $700 jan-08-1999

Let's get
Select store_name, sum (sales) from store_information group by store_name

Result:

Store_name sum (sales)

Los angeles $1800

San diego $250

Boston $700