By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
create table Todo_tbl (
id INT auto_increment,
person VARCHAR(45) ,
task VARCHAR(45) ,
duration INT(4),
deadline_day VARCHAR(2),
deadline_month VARCHAR(2),
PRIMARY KEY(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
insert into Todo_tbl values(1,'John', 'dust the floors', 40,04,03);
insert into Todo_tbl values(2,'Matt', 'do the dishes', 15,02,02);
insert into Todo_tbl values(3,'Mary', 'dusting', 40,03,02);
insert into Todo_tbl values(4,'Chloe', 'cleaning the windows', 65,04,05);
insert into Todo_tbl values(5,'John', 'wash the floors', 60,03,03);
insert into Todo_tbl values(6,'Bridget', 'take out the trash', 15,03,03);
insert into Todo_tbl values(7,'Matt', 'do the laundry', 18,02,02);
insert into Todo_tbl values(8,'Bridget', 'water the plants', 15,03,03);
select * from Todo_tbl;
create view Statistics_tbl as SELECT person,
SUM(duration) as total_duration
FROM Todo_tbl
GROUP BY person;
select * from Statistics_tbl;
drop view Statistics_tbl;
create table Statistics_tbl as SELECT person,
SUM(duration) as total_duration
FROM Todo_tbl
| version() |
|---|
| 8.0.30 |
| id | person | task | duration | deadline_day | deadline_month |
|---|---|---|---|---|---|
| 1 | John | dust the floors | 40 | 4 | 3 |
| 2 | Matt | do the dishes | 15 | 2 | 2 |
| 3 | Mary | dusting | 40 | 3 | 2 |
| 4 | Chloe | cleaning the windows | 65 | 4 | 5 |
| 5 | John | wash the floors | 60 | 3 | 3 |
| 6 | Bridget | take out the trash | 15 | 3 | 3 |
| 7 | Matt | do the laundry | 18 | 2 | 2 |
| 8 | Bridget | water the plants | 15 | 3 | 3 |
| person | total_duration |
|---|---|
| John | 100 |
| Matt | 33 |
| Mary | 40 |
| Chloe | 65 |
| Bridget | 30 |
Records: 5 Duplicates: 0 Warnings: 0
| person | total_duration |
|---|---|
| John | 100 |
| Matt | 33 |
| Mary | 40 |
| Chloe | 65 |
| Bridget | 30 |
CREATE TRIGGER todo_tbl_ins
AFTER INSERT ON Todo_tbl
FOR EACH ROW
BEGIN
UPDATE Statistics_tbl
SET total_duration = total_duration + NEW.duration
WHERE person = NEW.person;
END;
insert into Todo_tbl values(9,'John', 'dust the floors', 32,04,03);
select * from Statistics_tbl;
| person | total_duration |
|---|---|
| John | 132 |
| Matt | 33 |
| Mary | 40 |
| Chloe | 65 |
| Bridget | 30 |