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
– Bohemian ♦, Commented Jul 9, 2013 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
– Tyler DeWitt, Commented Jul 9, 2013 at 14:58

Bohemian · Accepted Answer · 2013-07-09 14:54:13Z

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 9, 2013 at 14:54

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler DeWitt Over a year ago

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.

Ezra Nugroho · Accepted Answer · 2013-07-09 15:00:37Z

1

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

answered Jul 9, 2013 at 15:00

Ezra Nugroho

2241 silver badge7 bronze badges

Collectives™ on Stack Overflow

postgresql aggregate of aggregate (sum of sum)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related