How can i do Calculation on an Auto Populated table,

Question

This seems to be a basic question but I can't seems to figure it out

I'v got 2 tables: table_a and table_b

---

SELECT * FROM `table_a`
+----+--------+----------+
| id | a_item | a_values |
+----+--------+----------+
|  1 |      1 |        5 |
|  2 |      1 |        5 |
+----+--------+----------+

---

SELECT * FROM `table_b`;
+--------+-------+
| a_item | total |
+--------+-------+
|      1 |    10 |
+--------+-------+
# NOTE: total is TEN  

---

As it shows I'm using a Trigger to SUM(a_values) from table_a, and store that results in table_b. The trigger I am using is:

CREATE TRIGGER totaling
    AFTER INSERT ON table_a
    FOR EACH ROW
    BEGIN
        INSERT INTO table_b(a_item, total)
        SELECT a_item, SUM(a_values) FROM table_a
        GROUP by a_item
        ON DUPLICATE KEY UPDATE
        total = VALUES(total);
    END

Now lets say, I update table_b:

UPDATE table_b
    SET total = total - 5
    WHERE a_item = 1

# NOTE: I'm subtracting FIVE from previous total which was TEN

and the result on table_b total column is 5, which is expected

SELECT * FROM `table_b`
+--------+-------+
| a_item | total |
+--------+-------+
|      1 |     5 |
+--------+-------+

Up to this point, everything is working accordingly However when I do a insert on table_a the calculations goes off. example:

INSERT INTO table_a(a_item, a_values) 
    VALUES
    (1, 5);

---

SELECT * FROM `table_b`;
+--------+-------+
| a_item | total |
+--------+-------+
|      1 |    15 |
+--------+-------+
# NOTE: see how the total has changed from FIVE to FIFTEEN, my expected result is TEN

I could fix this issue by directly updating on table_a, then the insert Trigger will only SUM with it's existing values which will give me my expected result.

the question is, is there a way where I could get the desired result without manipulating table_a, and only manipulating table_b, or do I need to change the insert Trigger ?

thanks.

Answer 1

I think the trigger you want is to maintain the sum in b, not to reset it. That would be:

CREATE TRIGGER totaling
AFTER INSERT ON table_a
FOR EACH ROW
BEGIN
    INSERT INTO table_b (a_item, total)
        VALUES (new.a_item, new.total)
        ON DUPLICATE KEY UPDATE total = total + VALUES(total);
END;

You probably also want an UPDATE trigger as well.

Answer 2

Consider not materializing the sum at all. Use a view instead, that provides the sum:

DROP TABLE table_b;

CREATE VIEW table_b
AS
SELECT a_item,
       sum(item_values) total
       FROM table_a
       GROUP BY a_item;

Then there's no need for any trigger and the sums will always be the accurate and current ones. Consistency is maintained all the time.

Answer 3

Do you want a TABLE table_b or a VIEW table_b? You cannot have both.

A TABLE is a bunch of data sitting on disk. A VIEW is not "materialized" (at least not in MySQL). It is just syntactic sugar for reading the data in the underlying table(s). Hence, the VIEW can only reflect what is currently in the underlying table.

You read a VIEW the same way you read a TABLE, namely with a SELECT.

When you add rows to the table, they are immediately there in the view. The sum is immediately recomputed when you read the view.

OK, "Some views are updatable and references to them can be used to specify tables to be updated in data change statements. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table." (quoting the manual). But don't do that. At least not until you have a better handle of tables and non-updatable views.

Answer 4

My solution for the problem was using a direct query on table_b. instead of summing and grouping from table_a, that used a trigger, a procedure was used for insertion on both tables

It inserted/updated table_b in itself:

INSERT INTO table_b(a_item, total)
    SELECT a_item, total + @var FROM table_b WHERE a_item = @var2
    GROUP BY a_item
    ON DUPLICATE KEY UPDATE
    total = VALUES(total);
# NOTE: the arithmetic operator doesn't have to be an addition.
# NOTE: @var is used as in a broad sense(not necessary as a MySQL variable).

Hope I've made myself clear, and somebody finds this answer helpful. cheers :)