1

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.

  • 1
    Maybe this is just because you simplified your question to the point that it no longer reflects the actual problem very well, but wouldn't it make a lot more sense to have "actual_qty" and "expected_qty" be separate columns rather than separate rows? – jjanes May 30 at 17:31
  • @jjanes Indeed, you are right. The thing is that I have "Quantity" as column, but also "Height", "Width", etc. So duplicating all these columns is indeed an option, yet one I'd like to avoid. – ebosi May 30 at 20:08
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.