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
>Solution :
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 .