Compute new records based on existing ones (e.g., sum of 2 records)? Background
I have an inventory where I register the number of fruits I have in stock — both expected and actual quantities.

For some fruits (e.g., here Banana and Cherry), I might not have both records.

My table looks then like this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |


This table gets partially updated over time (i.e. I might only change the Apple | Actual quantity, or add the Banana | Expected record). 

Question
➥ Is it possible to automatically compute new records based on the existing ones? If yes, how?

In our example, I might want to compute the Variation between the actual and the expected value: i.e. add the {fruit} | Variation | [{fruit | Actual}(Qty) − {fruit | Expected}(Qty)] record when I have both Actual and Expected records for a given {fruit}.

In our case, I'd like that my table becomes this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |
| Apple  | Variation  |   4 | ← newly computed record
| Durian | Variation  |  -1 | ← newly computed record


You can see that there is no Variation record for Banana or Cherry, because they are missing their Expected or Actual record, respectively.
I don't like the design, but sometimes we just have to deal with the what we find even if we don't like it.  Here is a query that gets you the 'Variation' part you are looking for:

select fruit, 'Variation' as recordtype, act_qty-exp_qty as qty from
   (select fruit, qty as exp_qty from foobar where recordtype ='Expected') as ex 
join 
   (select fruit,qty as act_qty from foobar where recordtype='Actual') as ac 
using (fruit);


You can either insert those results into the table (after clearing out the old results) or you can create a view with UNION ALL so that the Variation rows appear in the view, without ever physically existing.