Creating a calculated column based on the flag - TSQL

Question

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       |
+--------------+---------------+---------------+

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'.

The wanted result from the example table above:

+--------------+---------------+---------------+---------------+---------------+
|  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       |
+--------------+---------------+---------------+---------------+---------------+

I have tried the below code to get TotalAmount column:

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

Can someone help me with the Yes_amount column?

Answer 1

Use a CASE statement inside the sum() function.

Sample data

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);

Solution

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;

Result

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