Turn from: MySQL must know--group by and having
The group BY syntax can group the results of a query based on each member of a given data column, resulting in a grouped summary table.
The column name in the SELECT clause must be a grouping column or a column function, and the column function returns a result for each group defined by a 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 3 2009-10-11 2 John Doe Development 2500 3
2009-10-01 3 Harry Design Department 2600 5 2010-10-02 4 Wangliuqi Design Department 2300 4 2010-10-03 5 Ma VII Design Department 2100 4 2010-10-06 6 Zhao Ba Sales Department 5 2010-10-05 7 Money nine Sales Department 3100 7 2010-10-07 8< c26/> Sun 10 Sales Department 3500 7 2010-10-06
I want to list the results of the highest salary for each department, and the SQL statements are as follows:
[SQL]View PlainCopy
- Select Dept, Max (salary) as MAXIMUM
- From staff
- GROUP by DEPT
The query results are as follows:
Dept MAXIMUM
Development Department 4500 design department 2600
Sales Department 3500
Explain the result:
1, the column name in the "SELECT clause must be a grouping column or a column function", because select has the column dept contained in Group by;
2. The column function returns one result per group defined by the GROUP BY clause, and returns a result for each department according to the Department group, which is the highest salary for each department.
Use a WHERE clause with a GROUP BY clause
A group query can have a standard WHERE clause that eliminates unqualified rows before forming groups and calculating column functions. The WHERE clause must be specified before the GROUP BY clause
For example, find the highest salary for each level of the company's 2010 entry
[SQL]View PlainCopy
- SELECT Dept,edlevel,MAX (Salary) as MAXIMUM
- From staff
- WHERE hiredate > ' 2010-01-01 '
- GROUP by Dept,edlevel
The query results are as follows:
Dept Edlevel MAXIMUM
Design Department 4 2300
Design Department 5 2600
Sales Department 5 3000
Sales Department 7 3500
Each column name specified in the SELECT statement is also mentioned in the GROUP BY clause, and the column names not mentioned in these two places produce an error. The GROUP by clause returns one row for each unique combination of dept and Edlevel.
Use HAVING clause after GROUP by clause
You can group by applying a qualification so that the system returns results only to groups that meet the criteria. Therefore, a HAVING clause is included after the GROUP BY clause. Having is similar to where (the only difference is where filter rows, having filter groups) aving supports all where operators.
For example, find the highest and lowest salary for a department with more than 2 employees:
[SQL]View PlainCopy
- SELECT Dept,MAX (Salary) as MAXIMUM,MIN (Salary) as MINIMUM
- From staff
- GROUP by Dept
- Having COUNT (*) > 2
- ORDER by Dept
The query results are as follows:
Dept MAXIMUM MINIMUM
Design department 2600 2100 Sales Department 3500 3000
For example, find the highest salary and minimum salary for a department with an employee's average salary greater than 3000:
[SQL]View PlainCopy
- SELECT Dept,MAX (Salary) as MAXIMUM,MIN (Salary) as MINIMUM
- From staff
- GROUP by Dept
- Having AVG (salary) >
- ORDER by Dept
The query results are as follows:
Dept MAXIMUM MINIMUM
Sales Department 35003000
The group by and having of SQL