SQL Server How to Add Column Values to Another Row Based on Some Criteria?

oyjwcjzk 于 2023-02-21 发布在其他

关注(0)|答案(2)|浏览(242)

Let's say I have this table on MS SQL Server: Where I am trying to find the total numbers of active users per month of a specific year.

My database is a mess so some active users from December 2022 are being count as January and February 2023.
| Year | Month | Active_Users |
| ------------ | ------------ | ------------ |
| 2022 | 1 | 123 |
| 2022 | 2 | 143 |
| ... | ... | ... |
| 2022 | 12 | 100 |
| 2023 | 1 | 79 |
| 2023 | 2 | 3 |

How do I get the active users values from 2023 month 1 and 2 and add it to 2022 month 12 so I get a table like that:

Year	Month	Active_Users
2022	1	123
2022	2	143
...	...	...
2022	12	182

For any value greater than 2022 in the year column, the active users number should be added to active users in December 2022.

sql-server

来源：https://stackoverflow.com/questions/75514704/how-to-add-column-values-to-another-row-based-on-some-criteria

2条答案

按热度按时间

6yt4nkrj1#

You can use 2 parts of script

SELECT
Year, Month, count(Active_users)
FROM TABLE_NAME
GROUP BY Year, Month
HAVING Year = 2022 AND month < 12

UNION

SELECT
Year, Month, count(Active_users)
FROM TABLE_NAME
GROUP BY Year
HAVING (Year = 2023) OR (Year = 2022 AND month = 12)

赞(0）回复(0）举报 2023-02-21

monwx1rj2#

You can add the sum of all active users as to the active users of month 12 as you get a scalar value

SELECT YEAR,Month 
  , CASE WHEN Month = 12 THEN Active_users + (SELECT SUM(Active_users) FROM  mytable WHERE YEAR = 2023 AND Month IN (1,2))
  ELSE Active_users END Active_users
FROM mytable
WHERe YEAR = 2022

YEAR	Month	Active_users
2022	1	123
2022	2	143
2022	12	182

fiddle

赞(0）回复(0）举报 2023-02-21

我来回答

SQL Server How to Add Column Values to Another Row Based on Some Criteria?

2条答案

相关问题

热门标签

最新问答