Subtracting one column from another

I have a query that produces two columns. Each line of the columns is a sum of fields grouped by fields in the master table. I’m trying to create a third column that subtracts one column from the other.

In the image below I’m trying to subtract “PL_Total” from “Long_Basis” for each “Trade_id”. But I’m not having any success.

Can anyone suggest a way to make this work? Many thanks for any suggestions!

Hello DougKMiles, to subtract one column from another you could just add a column to your Query e.g. :
“Long_Basis” - “Leg_PL” AS “SUBTRACTED”

Thank you for offering this suggestion. I did try this however without any success. See Ratslinger’s answer above for the reasons.

yw:) … the above suggestion apparently works only for normal columns, not SUM’med… Takes a code guru such as Ratslinger to solve it, bravo again:)

It seems you have two problems here. One, you may not have an amount in a particular column. Second, you cannot in a simple statement calculate a difference while summing. To fix, first if there is no total substitute with a 0. This can be done with casewhen. Then select the totaled items and create the difference column based upon that. Test and SQL:

This statement cannot be done normally. Just go directly into SQL view (right click query & select Edit in SQL View..)

Here is the SQL statement in a clearer fashion:

SELECT "Trade_id", "PL_Total", "Long_Basis", "Long_Basis" - "PL_Total" "Difference" FROM
(SELECT "Trade"."Trade_id", casewhen(SUM( "Subtotal_4"."Leg_PL" ) IS NULL,0,SUM( "Subtotal_4"."Leg_PL" )) "PL_Total", casewhen(SUM( "Subtotal_4"."Long_Basis" )  IS NULL,0,SUM( "Subtotal_4"."Long_Basis" )) "Long_Basis" FROM "Subtotal_4", "Trade" WHERE "Subtotal_4"."Trade_id" = "Trade"."Trade_id" GROUP BY "Trade"."Trade_id")

I used your field/table names so you should be able to just copy & paste the statement.

If this answers your question please click on the :heavy_check_mark: (upper left area of answer).

Brilliant! Thank you so much for sharing your expertise Ratslinger! Greatly appreciated.

Doug Miles