sql - 聚合的 postgresql 聚合(总和)

Question

我有很多销售人员并且属于部门。我想看看某个部门每天的销售额。

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

例子:

部门:

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

worker :

| 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     |

部门员工

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

我想得到

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

要获得单个 worker 的总销售额，我可以做到

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

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

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