Vairis Vairis - 6 months ago 13
MySQL Question

MySQL Advanced SUM function

Good day!



I am sorry If this question was already asked, but I am kind of in hurry, so I am not sure.

I have 2 tables:



Table.Cars and Table.Price

In table Cars:

ID Auto_Increment Primary key NOT NULL
Name VARCHAR(30)


In table Price:

CarsID Primary key NOT NULL
Units Int
Price_per_unit decimal(10,2)
Total decimal(10,2)


So, what I need to do is, Create column (TOTAL* (Name does not need to be necessary this one)), in table Price, Set it to do Mathematical function.

Basicly, I want Column.Total to show Units * price_per_unit which would be:

ALTER TABLE Price
ADD TOTAL Decimal(10,2)

UPDATE Price SET Total = SUM(Units * Price_per_unit);


Which will look like this:

---------------------------------------------------
| ID | Units | Price_per_unit | Total | TOTAL |
---------------------------------------------------
| 1 | 12 | 30000,00 | 360000,00 | NULL |
| 2 | 5 | 12000,00 | 60000,00 | NULL |


And here comes my problem. I want to make TOTAL, to look like this:

---------------------------------------------------------
| ID | Units | Price_per_unit | Total | TOTAL |
---------------------------------------------------------
| 1 | 12 | 30000,00 | 360000,00 | 420000 |
| 2 | 5 | 12000,00 | 60000,00 | |
|_______________________________________________________|


So, next time, I put new record in table Price, I need TOTAL, to auto SUM my table by taking data from Units * Price_pre_unit.

So my question is, how do I make this query?

Answer

If you want Mysql database to do it automatically, it seems you are looking for a trigger. You should tun this code one time and the trigger will handle it for ever:

CREATE TRIGGER trigger_name
AFTER INSERT
   ON price FOR EACH ROW
BEGIN
   UPDATE Price SET Total = SUM(Units * Price_per_unit) where id=(select id from price order by id limit 1); 
END;
Comments