CODEWITHSUNDEEP
sqlpostgresqlselectjoinaggregate-functions
Tyler DeWitt
Tyler DeWitt

Reputation: 23576

postgresql aggregate of aggregate (sum of sum)

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?

Upvotes: 2

Views: 101

Answers (2)

Ezra Nugroho
Ezra Nugroho

Reputation: 224

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

Upvotes: 1

Bohemian
Bohemian

Reputation: 425358

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

Upvotes: 3

Related Questions