SQL Server Creating sum within groups on multiple variable

gv8xihay 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(112)

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

sql-server

来源：https://stackoverflow.com/questions/77179460/creating-sum-within-groups-on-multiple-variable

1条答案

按热度按时间

uajslkp61#

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 .

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Creating sum within groups on multiple variable

1条答案

相关问题

热门标签

最新问答