How to sum new and last inserted record with same id and insert result into new record

An approach

You have two mistakes in your approach that entail complexity.

• Any column that can be dumped like your AVERAGE should not be stored .

  If stored, it is a repeating column ... which causes the anomaly to be updated as you experience. The normalization point is to eliminate data duplication and thus eliminate update anomalies. It also eliminates complex code like this one as well as triggers, etc.

  Calculate SUM (), AVG (), etc. in the result set only , on the fly.

• Using identity columns, which basically means you have a record-writing system, no relational database. Without listing many of the problems it causes (I did it elsewhere), just named the problem here

  • You have a way of thinking.

  An identifier is a pointer to a physical record, it does not provide row uniqueness as required for relational databases.

  The ID is a pointer to the physical record, it doesn't mean anything, the user shouldn't see it. But you (and others) gave it meaning.

  You tie the glue to the physical structure of the file, not the logical data structure. This, in turn, complicates your code.

  So without giving you the corrected command CREATE TABLE
  
  , leaving yours as they are, let's pretend the ID and AVERAGE don't exist in the file.

The third element, unrelated to the approach, it seems that from Figure 10.58 you want to use kilometers per liter, whereas the arithmetic you detailed (liters per 100 km) would be 9.44. If you really want something in between, you better understand the elements.

Decision

    (Code obsolete due to revision)

      

        
        
        
      

    

Revised question

I was trying to get the numbers you gave while the question remained confusing (note the comments on this question). Since you have Revised your question, this requirement now becomes clear. Now it looks like you want (a) liters per 100 km [still not "average"], and (b) a total for each entry [sort of a grand total]. In this case, use this code.

The above note remains valid and applicable.

    SELECT  CARID,
            DATETIME,
            KM,
            LI,
            LPCK = ( LI_TOT / ( ( KM_LAST-KM_FIRST / 100 ) )  -- not stored
        FROM (
            -- create a Derived Table with KM_FIRST
            SELECT  CARID,
                    DATETIME,
                    -- not stored
                    KM_FIRST = (
                SELECT  MIN( KM )        -- get the first KM for car
                    FROM CONSUM
                    WHERE CARID = C.CARID
                    ),
                    KM_LAST = (
                SELECT  MAX( KM )        -- get the last KM for car
                    FROM CONSUM
                    WHERE CARID = C.CARID
                    ),
                    KM,                  -- KM for this row
                    LI,                  -- LI for this row
                    LI_TOT = (
                SELECT  SUM( LI )        -- get the total LI for car
                    FROM CONSUM
                    WHERE CARID = C.CARID
                    AND KM != (          -- exclude first LI for car
                    SELECT  MIN( KM )    -- get the first KM for car
                        FROM CONSUM
                        WHERE CARID = C.CARID
                        )
                    )
                FROM CONSUM C
            ) AS CONSUM_EXT

        ORDER BY CARID,
            DATETIME

      

        
        
        
      

    

Note that I am manipulating data, only data, no physical fields, we don't need to care about the physical aspects of the file. Liters per 100 km (what you call AVERAGE) is not saved and there is an update anomaly prevented. The total score for each record is calculated on the fly only during display.

This also fixes the problem /first entry

.

Of course it CARID

also doesn't make sense to the user.

Please feel free to comment or ask questions, etc.

Hard storage

There are many problems with storing the value that can be retrieved. This is hard coding at the storage layer. Of course, you can use a trigger to ease the pain, but it still won't work because (a) the principle is violated and (b) it violates existing engineering principles. For example. what happens when the LI for one line is entered incorrectly (eg 700.17) and subsequently corrected (eg 70.17)? All subsequent lines for this car are now incorrect and must be recalculated and updated. So now you need an Update trigger as well as an Insert trigger. Cancer compounds themselves.

The concept of "renewal anomaly", the prohibition on the storage of valuables that can be obtained, has been with us since 1970 for a good reason. We avoid them for a good reason.