The group by and having of SQL

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

1. Select Dept, Max (salary) as MAXIMUM
2. From staff
3. 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

1. SELECT Dept,edlevel,MAX (Salary) as MAXIMUM
2. From staff
3. WHERE hiredate > ' 2010-01-01 '
4. 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

1. SELECT Dept,MAX (Salary) as MAXIMUM,MIN (Salary) as MINIMUM
2. From staff
3. GROUP by Dept
4. Having COUNT (*) > 2
5. 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

1. SELECT Dept,MAX (Salary) as MAXIMUM,MIN (Salary) as MINIMUM
2. From staff
3. GROUP by Dept
4. Having AVG (salary) >
5. ORDER by Dept

The query results are as follows:

Dept   MAXIMUM   MINIMUM

Sales Department      35003000

The group by and having of SQL