Detailed explanation of group by and having in MySQL

The GROUP BY syntax can group and count the query results according to each member of a given data column, and finally obtain a group summary table.

The column name in the select clause must be a grouping column or a column function that 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 2009-10-11
  2 Li Si Development Department 2500 3 2009-10-01
  3 Wang Wu Design Department 2600 5 2010-10-02
  4 Wang Liu Design Department 2300 4 2010-10-03
  5 Maqi Design Department 2100 4 2010-10-06
  6 Zhao Ba Sales Department 3000 5 2010-10-05
  7 Qianjiu Sales Department 3100 7 2010-10-07
  8 Sun Ten Sales Department 3500 7 2010-10-06

I want to list the highest salary in each department. The SQL statement is as follows:

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 this result:

1. "The column name in the SELECT clause must be a grouping column or a column function" is satisfied because the SELECT has a column dept included in group by;

2. "The column function returns a result for each group defined by the group by clause". According to the department grouping, a result is returned for each department, which is the highest salary of each department.

Using the where clause with the group by clause

Grouped queries can have a standard where clause that eliminates non-qualifying rows before forming the groups and computed column functions. The where clause must be specified before the group by clause

For example, query the highest salary of each department and each level of the company who joined in 2010

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

Every column name specified in the SELECT statement is also mentioned in the GROUP BY clause. Column names not mentioned in both places will generate an error. The GROUP BY clause returns one row for each unique combination of dept and edlevel.

Using the Having Clause after the GROUP BY Clause

You can apply qualifications to groupings so that only results are returned for 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 that WHERE filters rows, HAVING filters groups). HAVING supports all WHERE operators.

For example, to find the highest and lowest salaries for departments with more than 2 employees:

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, to find the highest and lowest salaries in departments where the average salary of employees is greater than 3000:

SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM
FROM STAFF
GROUP BY dept
HAVING AVG(salary) > 3000
ORDER BY dept

The query results are as follows:

dept MAXIMUM MINIMUM
Sales Department 3500 3000

This is the end of this article about the detailed usage of MySQL group by and having. For more relevant MySQL group by and having content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!