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