postgresql aggregate of aggregate (sum of sum)

Question

I've got workers who have many sales and who belong to departments. I'd like to see how many sales a department is making per day.

For simplicity, let's say a worker belongs to only one department.

Example:

departments:

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

workers:

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

sales:

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

I'd like to get

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

To get the individual worker's total sales, I can do

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

How do I take those sums (the total amount sold per worker) and aggregate it by department?

Why is there a `department_employees` table? Shouldn't there be a `department_id` column on the `workers` table? — Bohemian, Jul 09 '13 at 14:56
@Bohemian - You're right for the way I phrased the question. In my real database an employee can belong to multiple departments, I just didn't want to worry about the logic on which department got credit if they belonged to multiple and I was more curious about the generic answer. Thanks for the catch though! — Tyler DeWitt, Jul 09 '13 at 14:58

score 3 · Accepted Answer · answered Jul 09 '13 at 14:54

3

Don't sum the sum, rather join from sales through the department_employees table to the department:

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

answered Jul 09 '13 at 14:54

Bohemian

412,405
93
575
722

Perfect, thanks! Also, just got back from Australia and saw a couple Wombats at an animal park. Best use of the profile picture I've seen in a while. – Tyler DeWitt Jul 09 '13 at 15:07

score 1 · Answer 2 · answered Jul 09 '13 at 15:00

Aggregate functions and group by work in a statement with joints too.

Try something like:

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

postgresql aggregate of aggregate (sum of sum)

2 Answers2