SQL Query Challenge. Divide by Subquery returning ‘Subquery returned more than 1 value.’ error

I have the following table in which I have two sales orders and each sales order has a different number of transactions.

SaleOrder	Transaction	Amount	Tax	Delivery Charge	TotalTax (Including Tax of Delivery Charge)
S1	T1	12	0	3	5.5 (This also includes Tax in Column 4)
S1	T2	27	4	3	5.5
S2	T1	15	2.5	5	6
S2	T2	10	2	5	6

In each SaleOrder, I’ve a different number of transactions and each transaction has different invoice amount in amount column plus tax of each transaction in tax column.

In sale order 1, I had a delivery charge of 6 which I divided by the number of transactions and got 3 against each. Similar is the case with sale order 2 where total delivery charge was 10.

Now in TotalTax column, I am getting the sum of tax of delivery charge and transaction repetitively.

How can I create a column in SQL Server with separate delivery charge tax, like this:

SaleOrder	Transaction	Amount	Tax	Delivery Charge	TotalTax	Delivery Charge Tax
S1	T1	12	0	3	5.5	0.75
S1	T2	27	4	3	5.5	0.75
S2	T1	15	2.5	5	7	1.25
S2	T2	10	2	5	7	1.25

Consider SaleOrder 1, where tax of transactions (0 + 4) and tax of delivery (1.5) equals 5.5 and it is posting against each transaction. I don’t have the need for TotalTax column but I need to separate 1.5 from it and divide against each transaction resulting in 0.75.

Similarly in SaleOrder 2, where tax of transactions (2 + 2.5) and tax of delivery (2.5) equals 7 and it is posting against each transaction. I need to separate 2.5 from it and divide against each transaction resulting in 1.75.

Can somebody help me on this?

Answer

You need to subtract the total of the tax column from the total_tax and then divide by the number of transactions. That uses window functions:

select t.*,
       (total_tax - sum(tax) over (partition by SaleOrder)) * 1.0 / count(*) over (partition by SaleOrder) as DeliveryChargeTax
from t;

Here is a db<>fiddle.