MySQL第08章_聚合函数
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;
SELECT *
FROM employees;
SELECT COUNT(commission_pct)
FROM employees;
SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;
SELECT AVG(commission_pct)
FROM employees;
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);