如何在 SELECT 字段中同时使用非数、计数和求和?

Question

我创建了一个包含以下列的表格：部门、员工人数和工资我想在第一列应用DISTINCT，在第二列应用COUNT，在最后一列应用SUM。

我的问题是：

SELECT DISTINCT d.department_name as departments, e.employee_id as NumbOfEmployees, e.salary as Salary
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
ORDER BY departments

此查询将生成下表：

department | numbofemployees | salary
----------   ---------------   ------
Accounting | 205             | 12000
Accounting | 200             | 5445
Admininstr.| 100             | 51651
Executive  | 101             | 100000
Executive  | 102             | 100000
Executive  | 103             | 100000

**注意：NumbofEmployees现在是员工ID号。

我想创建下表：

department | numbofemployees | salary
----------   ---------------   ------
Accounting | 2               | 17445
Admininstr.| 1               | 51651
Executive  | 3               | 300000

Answer 1

使用GROUP BY：

SELECT d.department_name as departments, 
COUNT(*) as NumbOfEmployees, 
SUM(e.salary) as Salary
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY departments