CODEWITHSUNDEEP
sqlsqlitetriggersaverage
Dan Riza
Dan Riza

Reputation: 477

SQLite select average from one table and update average field from another table using trigger

I have some problems with creating a trigger that will update values in a second table with average values from the first table. Since i am not very familiar with sqlite statements, maybe you guys can help me.

Here is my problem : i have one table table_1

table 1 description

and another table table_2

enter image description here

What i want is that table_2 "average_value" field to be updated with the average of values from table_1 that have the same field.

In this particular case resulting the aveage_value field in table 2 like this : 5 - for field_a 8 - for field_b 0 - for field_c

Any suggestions will help me alot ! Thank You.

Upvotes: 0

Views: 2772

Answers (1)

CL.
CL.

Reputation: 180210

  1. To compute the average value of a specific field dynamically, use this query:

    SELECT AVG(value)
FROM table_1
WHERE field = 'field_a';

  2. If you want instead a view that looks like your table_2, you can define it like this:

    CREATE VIEW view_2 AS
SELECT field,
       AVG(value) AS average_value
FROM table_1
GROUP BY field;

  3. If you really want table_2 to be an actual table, you could compute the average values like this:

    UPDATE table_2
SET average_value = (SELECT AVG(value)
                     FROM table_1
                     WHERE table_1.field = table_2.field);

  4. If you want table_2 to be an actual table, and want to update only those values that have changed, use a trigger like this:

    CREATE TRIGGER update_average_value_after_insert
AFTER INSERT ON table_1
FOR EACH ROW
BEGIN
    UPDATE table_2
    SET average_value = (SELECT AVG(value)
                         FROM table1
                         WHERE table1.field = NEW.field)
    WHERE field = NEW.field;
END;

    (You also need similar triggers for UPDATE and DELETE.)

Upvotes: 3

Related Questions