聚合的postgresql聚合(总和)

Question

我有一些员工，他们有很多销售人员，而且他们属于各个部门。我想看看一个部门每天的销售额是多少。

为简单起见，假设一个工人只属于一个部门。

示例：

部门：

| id | name            |
| 1  | Men's Fashion   |
| 2  | Women's Fashion |

员工：

| id  | name   |
| 1   | Timmy  |
| 2   | Sally  |
| 3   | Johnny |

销售额：

| id  |  worker_id | datetime          | amount |
| 1   |  1         | 2013-1-1 08:00:00 |  1     |
| 2   |  1         | 2013-1-1 09:00:00 |  3     |
| 3   |  3         | 2013-1-2 08:00:00 |  8     |

department_employees

| id | worker_id | department_id |
| 1  |   1       |    1          |
| 2  |   2       |    1          |
| 3  |   3       |    2          |

我想要

|  department     | amount |
| Men's Fashion   | 4      |
| Women's Fashion | 8      |

要获得单个工人的总销售额，我可以这样做

SELECT worker_id, SUM(amount) FROM sales
GROUP BY worker_id

如何获取这些金额(每个员工的销售总额)并按部门汇总？

Answer 1

不要对总和求和，而是通过department_employees表将sales连接到部门：

select d.name, sum(s.amount)
from sales s
join department_employees de on de.worker_id = s.worker_id
join departments d on d.id = de.department_id
group by d.name

Answer 2

聚合函数和group by work也包含在带有关节的语句中。

尝试如下所示：

SELECT name, SUM(amount) FROM departments, department_employees, sales
WHERE departments.id = department_employees.department_id 
AND sales.worker_id = department_employees.worker_id
GROUP BY name