06_mysql dql_ Group Query

# Group queries
/*
Grammar:
Select grouping functions, Columns (fields that appear in GROUP by)
From table
"Where filter Condition"
Group by group list (single field, multiple fields, function, expression)
"Having group after filtering"
"ORDER BY clause"

Execution order:
Table, pre-grouping filter, GROUP by sub-group, grouping statistics

Attention:
Query lists are special: grouping functions (statistical values, one set of values), fields that appear after GROUP by

Characteristics:
1, the filter condition in the group query divides into two kinds
Filter before grouping the original table filter condition is before the GROUP BY clause where keyword
Result filters After grouped filter groups are after the GROUP BY clause having the keyword

The grouping function makes the filter condition, which is definitely in the HAVING clause, and the filter after grouping
*/

# Introduction: Query average salary for each department
# Essence: Group--Small table
SELECT AVG (Salary), department_id # reserved one department_id per group
From Employees
GROUP by department_id;

# case 1: Query the maximum wage for each job "simple group query"
SELECT MAX (Salary), job_id
From Employees
GROUP by job_id;

# case 2: Query the number of departments for each work place
SELECT COUNT (*), location_id
From departments
GROUP by location_id;

# Add Filter criteria
# case 1: The average wage for each department is queried for a mailbox containing a character
SELECT AVG (Salary), department_id
From Employees
WHERE email like '%a% '
GROUP by department_id;

# case 2: The maximum wage for each of the leading employees with a bonus
SELECT MAX (Salary), manager_id
From Employees
WHERE commission_pct is not NULL
GROUP by manager_id;

# After adding a grouped filter condition "HAVING clause"
# case 1: The number of employees in which department >2
#1. Find out the number of employees in each department, intermediate results
SELECT COUNT (*), department_id
From Employees
GROUP by department_id;

#2. Based on the results of 1, filter out the end result of more than 2 employees
SELECT COUNT (*), department_id
From Employees
GROUP by department_id
Having COUNT (*) >2;

# case 2: Inquire about the maximum wage of the employee who has the bonus in each job >12000 the job number and salary
#1) Check the maximum wage "before grouping" for employees with bonuses in each job category
SELECT MAX (Salary), job_id
From Employees
WHERE commission_pct is not NULL
GROUP by job_id;

#2) on the basis of 1, filter out the maximum wage "12000" after grouping the screening
SELECT MAX (Salary), job_id
From Employees
WHERE commission_pct is not NULL
GROUP by job_id
Having MAX (salary) >12000;

# Case 3: Query leader number >102 the minimum wage under each leader 5000 of the leader number, and the minimum wage
# 1. Query leader number >102 the minimum wage under each lead "before grouping filter"
SELECT MIN (Salary), manager_id
From Employees
WHERE manager_id>102
GROUP by manager_id;

# 2. On the basis of 1, the minimum wage is screened at 5000 of the minimum wage, and the leader number "filter after grouping"
SELECT MIN (Salary), manager_id
From Employees
WHERE manager_id>102
GROUP by manager_id
Having MIN (salary) >5000;

# By expression/function, group query
# case 1: Group by Employee name length, query the number of each group of employees, filter out the number of employees >5
#1 GROUP by Employee name length, query the number of employees per set
SELECT COUNT (*), LENGTH (last_name)
From Employees
GROUP by LENGTH (last_name);

Add filters after #2 grouping
SELECT COUNT (*), LENGTH (last_name)
From Employees
GROUP by LENGTH (last_name)
Having COUNT (*) >5;

# Group queries by multiple fields
# case 1: Query the average salary for each category of employee in each department
SELECT AVG (Salary), job_id,department_id
From Employees
GROUP by department_id,job_id; # first grouped by department, then by category

# Add Sort
# case 1: Query the average salary of each job in each department, show the result of higher than 10,000 average salary from high to low
SELECT AVG (Salary), department_id,job_id
From Employees
WHERE department_id is not NULL
GROUP by department_id,job_id
Having AVG (salary) >10000
ORDER by AVG (salary) DESC; # sort the final result

06_mysql dql_ Group Query