Turn: MySQL GROUP by

Group by usage resolves the group by syntax to group the query results by 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. The column function returns a result for each group defined by the GROUP BY clause. An employee information sheet structure and data are as follows:  id  name  dept  salary  edlevel  hiredate        1 Zhang Three Development Department 3 2009-10-11      2 Li Four Development Department 2500 3 2009-10-01    &N Bsp  3 Harry Design Department 2600 5 2010-10-02      4 Wangliuqi Design Department 2300 4 2010-10-03      5 ma seven Design department 2100 4 2010-10-06      6 Zhao Eight sales Department 5 2010-10-05      7 money nine sales Department 3100 7 2010-10-07      8 Sun 10 Sales 3500 7 2010-10-06  For example, I want to list the results of the highest salary per department, the SQL statements are as follows: SELECT DEPT, MAX ( SALARY) as Maximumfrom staffgroup by DEPT query results as follows:      dept  maximum        Development Department 2500      Design Department 2600      Sales department 3500 Explain this result: 1, The column names in the "SELECT clause must be a grouping column or a column function" because select has the columns contained in the GROUP by Dept dePt. 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. Note: The calculation is for each department (the group defined by the groups by clause) instead of the entire company's MAX (SALARY). For example, query the total number of salaries per department select DEPT, SUM (SALARY) as Totalfrom staffgroup by DEPT query results:dept  total  Development 4500 Design Department 7000 sales Department 9600 use a grouping query with a WHERE clause with a GROUP BY clause to have a standard where clause that eliminates unqualified rows before forming groups and calculating column functions. You must specify a WHERE clause before the GROUP BY clause. For example, check the company's 2010 entry for each department in each level of the highest salary of select DEPT, EDLEVEL, MAX (SALARY) as Maximumfrom staffwhere hiredate > ' 2010-01-01 ' Grou P by DEPT, Edlevelorder by DEPT, EDLEVEL query results are as follows:  dept  edlevel  maximum     & nbsp Design Department 4 2300      Design Department 5 2600      Sales Department 5 3000    & nbsp Sales Department 7 3500 Note: Each column name specified in the SELECT statement is also mentioned in the GROUP BY clause. 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. After the GROUP BY clause, the HAVING clause is used to group the qualifying conditions so that the system returns results only to groups that meet the criteria. To do this, include a HAVING clause after the GROUP BY clause. A HAVING clause can contain one or more predicates that are joined with and and OR. Each predicate compares a group attribute, such as AVG (SALARY), with one of the following: for example: Looking for the highest and lowest salary for a department with more than 2 employees: SELECT DEPT, MAX (SALARY) as MAXIMUM, MIN (SALARY) as Minimumfrom Staffgroup by Depthaving COUNT (*) >2order by DEPT query results are as follows:  dept  MAXI mum  minimum       Design Department 2600 2100      Sales Department 3500 3000 For example: Find the highest and lowest salary of the Department of the employee average salary greater than 3000: SELECT DEPT, MAX (SALARY) as MAXIMUM, MIN (SALARY) as Minimumfrom Staffgroup by Depthav ING AVG (SALARY) >3000order by DEPT query results as follows:  dept  maximum  minimum        Sales Department 3500 3000

Goto: MySQL GROUP by