CODEWITHSUNDEEP
phpmysql
Aleksa
Aleksa

Reputation: 99

How to sum new and last inserted entry with same ID and insert result in new entry

I'm trying to make automatic sum of average consumption of fuel using PHP and MYSQL. But I do not know how to do it. This is explanation:

Tabe CONSUM:

ID   CARID   LI        KM          DATETIME         AVERAGE
--------------------------------------------------------------
6     9     70.17   174857   2015-02-14 12:58:51      9.44
5     5     51.00   154785   2015-02-13 10:11:19      8.73
4     8     99.44   485627   2015-02-12 11:45:48      6.84
3     9     47.78   174114   2015-02-11 10:21:32  /first entry
2     8     24.74   484175   2015-02-10 10:28:37  /first entry
1     5     89.65   154201   2015-02-09 10:01:14  /first entry

*Data as an example how I want to look like. Everything works except AVERAGE colum, that's why I'm here.

I'm trying to make php function that will make sum for every new entry of new and last KM entry with same CAREID something like this (exemple for CARID 9) :

I spent a lot of time trying to make this working but simply I never been even close.

Upvotes: 3

Views: 498

Answers (5)

PerformanceDBA
PerformanceDBA

Reputation: 33808

Approach

You have two errors in your approach, which introduces complexity.

  1. Any column that can be derived, such as your AVERAGE should not be stored.

    If it is stored, it constitutes a duplicate column ... which leads to an Update Anomaly, as you are experiencing. The point of Normalisation is to eliminate data duplication, and thus eliminate Update Anomalies. It also eliminates complex code such as this, as well as triggers, etc.

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

  2. Use of ID columns, which basically means you have a Record Filing System, no a Relational Database. Without enumerating the many problems that it causes (I have done that elsewhere), just naming the problem here

    • you have an ID mindset.

    The ID is a physical record pointer, it does not provide row uniqueness, as required for Relational Databases.

    The ID is a physical record pointer, it means nothing, the user should not see it. But you (and others) have given it meaning.

    Which glues you to the physical structure of the file, rather than the logical structure of the data. Which in turn complicates your code.

    Therefore, without giving you a corrected CREATE TABLE command, leaving yours as is, let us pretend that the ID, and the AVERAGE, do not exist in the file.

A third item, not related to approach, it seems that from the figure given, 10.58, you want Kilometres per litre, whereas the arithmetic you have detailed (Litres per 100 Km) will produce 9.44. If you do want an average of some kind, you are better off figuring out the elements first.

Solution

    (Code obsolete due to revision)

Revised Question

I was attempting to obtain the figures you gave, while the question remained confused (note the comments to that effect). Since you have Revised your question, the requirement is now clear. It now appears you want (a) Litres per 100 Km [still not an "average"], and (b) an overall figure for each record [a kind of running total]. In that case, use this code.

The notes above remain 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

Notice I am manipulating the data, and only the data, no physical fields, we shouldn't care about the physical aspects of the file. Litres per 100 Km (what you are calling AVERAGE) is not stored, and there an Update Anomaly is avoided. The overall figure for each record is calculated "on the fly", at display time only.

This also eliminates your /first entry issue.

Of course, CARID as well is meaningless to the user.

Please feel free to comment or ask questions, etc.

Hard Storing

There are many problems with storing a value that can be derived. This is Hard-coding at the data storage level. Sure, you can use a trigger to ease the pain, but it still won't work, because (a) the principle is broken and (b) it breaches existing engineering principles. Eg. what happens when the LI for a single row is incorrectly entered (eg. 700.17), and subsequently corrected (eg. 70.17)? All the subsequent rows for that car are now incorrect, and have to be re-calculated, and updated. So now you need an Update trigger as well as an Insert trigger. Cancer compounds itself.

The concept of an Update Anomaly, the prohibition of storing values that can be derived, have been with us since 1970, for good reason. We avoid them, for good reason.

Upvotes: 5

pala_
pala_

Reputation: 9010

It seems to me that the appropriate way to do this is with a BEFORE INSERT trigger. Such a trigger may look like this:

delimiter //
create trigger avg_calc before insert on consum 
  for each row 
  begin
    declare lastOdo int;       -- variable to hold the last odometer reading
    select km
      into lastOdo             -- store the last reading here
      from consum
      where carid = NEW.carid  -- for the carid we are inserting
      order by `datetime` desc -- get the last one by date
      limit 1;
    set NEW.average = (NEW.km - lastOdo) / NEW.li;  -- update the average we're about to insert
  end//
delimiter ;

This will then automatically average the last two entries per car every time a new entry is inserted for that car.

demo here

Upvotes: 2

Sean
Sean

Reputation: 12433

Your AVERAGE where CARID=5 & CARID=8 does not compute the same as where CARID=9, so my example does not exactly match, but if you are trying to this on an insert, you could do something like

INSERT INTO CONSUM
SELECT 
    6,
    9,
    70.17,
    174857,
    '2015-02-14 12:58:51', 
    ROUND((174857-a.KM)/70.17, 2) 
FROM CONSUM a
WHERE a.CARID = 9 
ORDER BY ID DESC 
LIMIT 1;

sqlfiddle example - http://sqlfiddle.com/#!9/dce1d/1

Upvotes: 0

Falt4rm
Falt4rm

Reputation: 910

I'll post anyway. My idea was :

  • Using the last 2 rows (New entry & last entry) using the array return. Therefore i could use count(array) - 1 & -2.

.

<?php 

include("./inc.connect.php");

$Query =  "SELECT id, km, li
            FROM consum
            WHERE cardid = 9";


$users = $db->query($Query);

$array_res = $users->fetchAll();
$nb_rows = count($array_res);

$diff_km = $array_res[($nb_rows - 1)]['km'] - $array_res[($nb_rows - 2)]['km'];

$new_li = number_format(($array_res[($nb_rows - 1)]['li'] / ($diff_km * 0.01)),2);

print "<pre>";
print_r($array_res);
print "</pre>";

echo "diff km : " . $diff_km . " new_li : " . $new_li . "<br>";

$UpdateQuery = "UPDATE consum SET average = '$new_li' WHERE id = " .     
                 $array_res[($nb_rows - 1)]['id'];

/* Begin a transaction, turning off autocommit */
try 
{  
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $db->beginTransaction();

   $sth = $db->exec($UpdateQuery);
   $db->commit(); 
} 
catch (Exception $e) 
{
   $db->rollBack();
   echo "Failed: " . $e->getMessage();
 }
?>

Result : 

Array
(
[0] => Array
    (
        [id] => 3
        [0] => 3
        [km] => 174114
        [1] => 174114
        [li] => 47.78
        [2] => 47.78
    )

[1] => Array
    (
        [id] => 6
        [0] => 6
        [km] => 174857
        [1] => 174857
        [li] => 70.17
        [2] => 70.17
    )

)

diff km : 743 new_li : 9.44

UPDATE consum SET average = '9.44' WHERE id = 6

I did the maths - It's correct 70.17/7.43 = 9.44

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

The following query gets the last id for each car:

select c.*,
       (select c2.id
        from consum c2
        where c2.carid = c.carid and c2.id < c.id
        order by c2.id desc
        limit 1
       ) as last_id
from consum c;

Next, for the information you want, you can join back to the table to get the full record and then do the calculation:

select c.ID, c.CARID, c.LI, c.KM, c.DATETIME,
       c.li / (c.km - cprev.km) / 100) as avg
from (select c.*,
             (select c2.id
              from consum c2
              where c2.carid = c.carid and c2.id < c.id
              order by c2.id desc
              limit 1
             ) as last_id
      from consum c
     ) c left join
     consum cprev
     on c.last_id = cprev.id;

Upvotes: 1

Related Questions