MySQL essential basics: grouping function, aggregate function, grouping query detailed explanation

SELECT SUM(salary) AS sum, AVG(salary) AS average, MAX(salary) AS maximum, MIN(salary) AS minimum, COUNT(salary) AS number FROM employees;

SELECT SUM(DISTINCT salary) AS sum, AVG(DISTINCT salary) AS average, COUNT(DISTINCT salary) AS number of duplicates removed, COUNT(salary) AS number of non-duplicates FROM employees;

#Equivalent to the method of counting rows SELECT COUNT(*) 
FROM employees;

#Equivalent to the second method of counting rows, where 1 can be replaced by other constants or fields SELECT COUNT(1) 
FROM employees;

#[] contains optional SELECT grouping functions and lists (required to appear after GROUP BY)
FROM table [WHERE filter condition]
GROUP BY grouping list [ORDER BY clause]

#Query the highest salary for each job type SELECT MAX(salary) AS highest salary, job_id
FROM employees
GROUP BY job_id;

#Query the average salary of employees whose emails contain a in each department (screening before grouping)
SELECT AVG(salary) AS average salary, department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#Query the number of employees in departments where the number of employees is greater than 2 (screening after grouping)
#Using HAVING
SELECT COUNT(*) AS employee_number,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#SELECT COUNT(*) AS number of employees, job_id, department_id by multiple fields
FROM employees
GROUP BY job_id,department_id;

#Complete structure SELECT AVG(salary) AS average salary, department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary)>9000
ORDER BY AVG(salary) DESC;