Ask Your Question

subtracting one column from another

asked 2017-04-30 00:39:30 +0200

DougKMiles gravatar image

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!

image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-04-30 03:17:40 +0200

Ratslinger gravatar image

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:

image description

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

edit flag offensive delete link more


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

Doug Miles

DougKMiles gravatar imageDougKMiles ( 2017-04-30 15:25:05 +0200 )edit

answered 2017-04-30 02:33:55 +0200

librebel gravatar image

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"

edit flag offensive delete link more


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

DougKMiles gravatar imageDougKMiles ( 2017-04-30 15:26:02 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-04-30 16:02:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-04-30 00:39:30 +0200

Seen: 1,085 times

Last updated: Apr 30 '17