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

Solution 1:

Use a CASE statement inside the sum() function.

Sample data

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

insertinto@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 (PARTITIONBY [ORDER]) AS'TOTAL_AMOUNT',
        SUM(casewhen [Flag]='Yes'then AMOUNT else0end) OVER (PARTITIONBY [ORDER]) AS'Yes_AMOUNT'from@MyTable mt;

Result

OrderFlagAmountTOTAL_AMOUNTYes_AMOUNT------------------------------------------------Order1Yes500825825Order1Yes325825825Order2Yes7992024         1474Order2No5502024         1474Order2Yes6752024         1474Order3No8008000