I have a table in Microsoft SQL Server with municipality IDs, company IDs and number of employees and I want to get the sum of employees per municipality and also the sum of employees per company Id and per municipality. So the table currently looks like this
| Municipality | Company Type ID | Number of Employees | Total employees in same municipality | Total Employees in same economic sector and same Municipality |
|--------------|-----------------|---------------------|--------------------------------------|---------------------------------------------------------------|
| 03212 | 10332 | 0 | | |
| 03212 | 10332 | 12 | | |
| 03212 | 10332 | 2 | | |
| 03212 | 10332 | 3 | | |
| 03212 | 10333 | 4 | | |
| 03212 | 10333 | 1 | | |
| 03133 | 10554 | 4 | | |
| 03133 | 10554 | 55 | | |
| 03133 | 10554 | 4 | | |
But I want to achieve something like this
| Municipality | Company Type ID | Number of Employees | Total employees in same municipality | Total Employees in same company ID and same Municipality |
|--------------|-----------------|---------------------|--------------------------------------|----------------------------------------------------------|
| 03212 | 10332 | 0 | 22 | 17 |
| 03212 | 10332 | 12 | 22 | 17 |
| 03212 | 10332 | 2 | 22 | 17 |
| 03212 | 10332 | 3 | 22 | 17 |
| 03212 | 10333 | 4 | 22 | 5 |
| 03212 | 10333 | 1 | 22 | 5 |
| 03133 | 10554 | 4 | 63 | 59 |
| 03133 | 10554 | 55 | 63 | 59 |
| 03133 | 10555 | 4 | 63 | 4 |
I have tried using the SUM OVER PARTITION but that only allows me to do it for one variable. How can I create the sum and insert it into the table over multiple variables
Sum(no of employees) over (partition by company type, Municipality) as 'Total Employees by Municipality
Something like that. It's just another column like you did previously .
I'm on mobile so formatting isn't correct .