基于标志创建计算列 - TSQL [英] Creating a calculated column based on the flag - TSQL

问题描述

我想根据标志计算订单的总和.示例表:

I want to calculate the sum of the orders based on the flag. Sample table:

+--------------+---------------+---------------+
|  Order       |     Flag      |     Amount    |
+--------------+---------------+---------------+
| Order1       |     Yes       |     500       |
| Order1       |     Yes       |     325       |
| Order2       |     Yes       |     799       |
| Order2       |     No        |     550       |
| Order2       |     Yes       |     675       |
| Order3       |     No        |     800       |
+--------------+---------------+---------------+

我想创建 2 个新列.

I want to create 2 new columns.

一栏是订单总额，另一栏是订单总额，其中标志为是".

One column is the total amount of order and another one is the total amount of order where the flag is 'yes'.

上例表中想要的结果:

+--------------+---------------+---------------+---------------+---------------+
|  Order       |     Flag      |     Amount    |  TotalAmount  | Yes_amount    |
+--------------+---------------+---------------+---------------+---------------+
| Order1       |     Yes       |     500       |     825       |     825       |
| Order1       |     Yes       |     325       |     825       |     825       |
| Order2       |     Yes       |     799       |     2024      |     1474      |
| Order2       |     No        |     550       |     2024      |     1474      |
| Order2       |     Yes       |     675       |     2024      |     1474      |
| Order3       |     No        |     800       |     800       |       0       |
+--------------+---------------+---------------+---------------+---------------+

我尝试了以下代码来获取 TotalAmount 列:

I have tried the below code to get TotalAmount column:

SUM(AMOUNT) OVER (PARTITION BY ORDER) AS TOTAL_AMOUNT.

有人可以帮我填写 Yes_amount 列吗?

Can someone help me with the Yes_amount column?

推荐答案

使用 CASE 语句在 sum() 函数中.

示例数据

declare @MyTable table
(
    [Order] nvarchar(10),
    [Flag] nvarchar(3),
    [Amount] int
);

insert into @MyTable ([Order], [Flag], [Amount]) values
('Order1', 'Yes', 500),
('Order1', 'Yes', 325),
('Order2', 'Yes', 799),
('Order2', 'No',  550),
('Order2', 'Yes', 675),
('Order3', 'No',  800);

解决方案

select mt.[Order], mt.[Flag], mt.[Amount],
        SUM(AMOUNT) OVER (PARTITION BY [ORDER]) AS 'TOTAL_AMOUNT',
        SUM(case when [Flag]='Yes' then AMOUNT else 0 end) OVER (PARTITION BY [ORDER]) AS 'Yes_AMOUNT'
from @MyTable mt;

结果

Order      Flag Amount      TOTAL_AMOUNT Yes_AMOUNT
---------- ---- ----------- ------------ -----------
Order1     Yes  500         825          825
Order1     Yes  325         825          825
Order2     Yes  799         2024         1474
Order2     No   550         2024         1474
Order2     Yes  675         2024         1474
Order3     No   800         800          0

这篇关于基于标志创建计算列 - TSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！