• derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,681

Topic: Problem with calculated field

Hello Dmitry,

I hope you can help.  I am using a calculated field (medicine.stock) to show, for each drug in my 'medicines' table, the (sum of drugs in) - (sum of drugs out).  This is the calculation:

(select sum(inwards.inqty)
from inwards
where medicine.id = inwards.id_medicine)
-
(select sum(out.outqty)
from out
where medicine.id = out.id_medicine)

This works as I want but ONLY after I have received some drugs ('inwards' table) and despatched some drugs ('out' table) - see rows 1-5 in the attached screen-shot (medicinechest1.jpg).  But if I have only received drugs and NOT despatched any, the calculation is not working - see rows 6-7 (medicinechest1.jpg).  I need to do some test for zero but I cannot get the syntax right.   Any ideas?

I'm using Windows XP and MVD 2.3 beta.

Thanks,
Derek

Post's attachments

Attachment icon medicinechest dmitry.zip 1.34 mb, 517 downloads since 2016-01-11 

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Problem with calculated field

Hello,


Try to use TOTAL instead SUM

(select TOTAL(inwards.inqty)
from inwards
where medicine.id = inwards.id_medicine)
-
(select TOTAL(out.outqty)
from out
where medicine.id = out.id_medicine)


The sum() and total() aggregate functions return sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.

Dmitry.
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,681

Re: Problem with calculated field

Hey Dmitry,
That works perfectly now.
Thanks for the help - and for the super fast reply.
Derek.