I have 2 tables TESTUserInfo and BlockRanking.
TestUserInfo is a bunch of users info and totals for their specific user. Each user also has a zone associated with their user.
In BlockRanking I combine all of the users with each zone and sum all of the total points for that zone.
I have this query to insert values into table BlockRanking from TESTUserInfo and it works.
INSERT INTO BlockRanking (zone, total)
SELECT zone, SUM(total)
GROUP BY zone
ORDER BY SUM(total) DESC
You can use triggers to run tasks when certain queries are run on a table. A trigger runs once for each inserted row. Instead of aggregating, we can keep track of the totals that are added into the TESTUserInfo table as they arrive.
CREATE TRIGGER update_totals AFTER INSERT ON TESTUserInfo FOR EACH ROW BEGIN INSERT INTO BlockRanking (zone, total) VALUES (NEW.zone, NEW.total) ON DUPLICATE KEY UPDATE total = total + VALUES('total'); END;
zone would have to be a unique index in
If it is possible for rows to be updated or removed in TESTUserInfo, you would similarly have to create triggers. For an
UPDATE trigger, you could do something like:
total = total + NEW.total - OLD.total.