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 
(upper left area of answer).
Brilliant! Thank you so much for sharing your expertise Ratslinger! Greatly appreciated.
Doug Miles