SQL Server Change field conditional statement

h43kikqp 于 2023-03-11 发布在其他

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

I've got a table which looks like:

>Date--Country--Courier--Division--Volume
>
>datetime--varchar--varchar--varchar--varchar--int
>
>datetime--varchar--varchar--varchar--varchar--int

All this data is gathered in a temp table and represents a month worth data.

The final scope is to change the countries which have TOTAL volume less than a value to 'others'.

FOR example:

>Country -- Volume
>
>Germany -- 1200
>
>Albania -- 250

I want to change it to:

>Country -- Volume
> 
>Germany -- 1200
>
>Other -- 250

I've been trying to make an IF statement based on

SUM(Volume) OVER(PARTITION BY Country) < x

But it looks like I m far from achieving this.

Would appreciate any help.

Thank you.

sql-server

来源：https://stackoverflow.com/questions/52946866/change-field-conditional-statement

1条答案

按热度按时间

6ojccjat1#

From a logic point of view, you could proceed in the following way:

DECLARE @threshold int = 250;
SELECT
   0 AS Priority, 
   Country, 
   Volume
   FROM YourTable
   WHERE Volume >= @threshold
UNION ALL
SELECT 
   1 AS Priority, 
   'Others' AS Country,
   SUM(Volume) AS Volume
FROM YourTable
WHERE Volume < @threshold
ORDER BY Priority, Volume DESC

Given that yor data could be:

Country - Volume
----------------
Germany - 1000
Italy   - 500
France  - 300
Albania - 120
Spain   - 100
Sweden  - 90

You'd get:

Country - Volume
----------------
Germany - 1000
Italy   - 500
France  - 300
Others  - 310 -- equals to sum of countries under @threshold value

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server Change field conditional statement

1条答案

相关问题

热门标签

最新问答