Detailed description of the use of group by-reprinted

Group
Group
A general database can analyze data based on specific table columns.
You can group rows according to the group defined in the group by clause. In its simplest form, a group is composed of columns called grouping columns. 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 following example generates a result listing the highest salaries of each department number:
Select Dept, max (salary) as maximum
From staff
Group by Dept
This statement produces the following results:
Dept maximum
---------------
10 22959.20
15 20659.80
20 18357.50
38 18006.00
42 18352.80
51 21150.00
66 21000.00
84 19818.00
Note: Each department is calculated (the group defined by the Group by clause) rather than the max (salary) of the entire company ).
Use the WHERE clause with the group by clause
Grouping query can be used in grouping and column calculation functions.BeforeA standard where clause that can eliminate non-qualified rows. The where clause must be specified before the Group by clause. For example:
Select workdept, edlevel, max (salary) as maximum
From employee
Where hiredate> '2017-01-01'
Group by workdept, edlevel
Order by workdept, edlevel

Result:
Workdept edlevel maximum
--------------------------
D11 17 18270.00
D21 15 27380.00
D21 16 36170.00
D21 17 28760.00
E11 12 15340.00
E21 14 26150.00
Note: Each column name specified in the SELECT statement is also mentioned in the group by clause. The column names that are not mentioned in these two places produce errors. The group by clause returns one row for each unique combination of workdept and edlevel.

 
Use having clause after the group by clause
You can apply conditions to group so that the system only returns results for groups that meet the conditions. Therefore, the Group by clause contains a having clause. A having clause can contain one or more predicates connected with and or. Each predicate compares a group feature (such as AVG (salary) with one of the following:
Another feature of this group
For example:
Having AVG (salary)> 2 * min (salary)
Constant
For example:
Having AVG (salary) & gt; 20000
For example, the following query queries the highest and lowest salaries of departments with more than 4 employees:
Select workdept, max (salary) as maximum, min (salary) as minimum
From employee
Group by workdept
Having count (*)> 4
Order by workdept
This statement produces the following results:
Workdept maximum minimum
------------------------------
D11 32250.00 18270.00
D21 36170.00 17250.00
E11 29750.00 15340.00
It is possible (though rare) that a query has a having clause but does not have a group by clause. In this case, DB2 regards the entire table as a group. Because the table is regarded as a single group, there can be a maximum of one result row. If the having condition is true for the entire table, the selected result is returned (the result must be composed of column functions); otherwise, no rows are returned.

The official statement is to operate by group,

Or the point of understanding is to operate by class ..

You can see the following example:

ID name warehouse quantity
01 watermelon 1 10
02 watermelon 2 30
03 rice 1 30
04 Apple 1 40
05 Apple 2 45
06 apple 3 5

Select name, Sun (price) from Table group by name

The preceding SQL statement returns the total number of items, regardless of the warehouse ..

Result:

Watermelon, 40
Rice, 30
Apple, 90

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/huluhulu88/archive/2008/03/13/2179316.aspx