CODEWITHSUNDEEP
mysql
Massimo Griffani
Massimo Griffani

Reputation: 837

How insert the sum from other tables in mysql?

I have two tables

TABLE ORDINIUA

| ordiniua(pk) | fornitore | elemento | stimaarrivo | arrivato |
----------------------------------------------------------------
|   8ec230     |   ABB     |    17    |  10/10/2017 |    0     |
|   8ec520     |   STE     |    147   |             |    1     |
|   8ec234     |   FIS     |    17    |  25/06/2017 |    1     |

TABLE QUADRI

| elemento(pk) | numeroco  |descrizione| colonne | Ordini_Arrivati|
------------------------------------------------------------------
|   22         | 251.06/17 |           |    6    |    the sum     |
|   17         | 168.05/17 |           |    0    |    the sum (1) |

Whre I wrote "the sum" i'd like to have the sum of table Ordiniua field "arrivato" where "elemento" is the same. e.g.: arrivato on 8ec520 is 0, arrivato on 8ec234 is 1, the sum is 1.

Is it possible to aumatically generate this value (like excel "sum if") ? If yes how?

Upvotes: 1

Views: 40

Answers (1)

Ricardo Faria
Ricardo Faria

Reputation: 794

To do what you want you should use MySQL Triggers. When you INSERT a new row:

DELIMITER $$

CREATE TRIGGER insertDatabase
BEFORE INSERT ON ordiniua
FOR EACH ROW
   BEGIN
       UPDATE quadri s
           SET s.Ordini_Arrivati = s.Ordini_Arrivati + new.arrivato
       WHERE s.elemento = new.elemento;
END;$$
DELIMITER ;

And when you UPDATE an existing row. Be careful about the UPDATE because you will have to know the difference between the previous value and the current value inside the arrivato column.

DELIMITER $$

CREATE TRIGGER updateDatabase
BEFORE UPDATE ON ordiniua
FOR EACH ROW
   BEGIN
       UPDATE quadri s
           SET s.Ordini_Arrivati = s.Ordini_Arrivati + new.arrivato
       WHERE s.elemento = new.elemento;
END;$$
DELIMITER ;

You could want to have a Trigger to DELETE, but as you can see you just have to follow the logic. Wish it could help you

Upvotes: 1

Related Questions