Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can a query multiply 2 cell for each row MySQL?

I want to multiply 2 cells for each row and put the value of that in the last column called Total. Can this be done by a normal query?

Example:

Pieces | Price | Total
6      |   4   |  null // should be 24
2      |  10   |  null // should be 10
like image 365
Arne Nouwynck Avatar asked Apr 17 '11 11:04

Arne Nouwynck


People also ask

How do you multiply rows in MySQL?

MySQL - Multiplication Operator (*) This operator is used to multiply two numbers in MySQL.

Can you multiply in SQL query?

The SQL multiply ( * ) operator is used to multiply two or more expressions or numbers.

How do you multiply rows in SQL?

All you need to do is use the multiplication operator (*) between the two multiplicand columns ( price * quantity ) in a simple SELECT query. You can give this result an alias with the AS keyword; in our example, we gave the multiplication column an alias of total_price .


3 Answers

Use this:

SELECT 
    Pieces, Price, 
    Pieces * Price as 'Total' 
FROM myTable
like image 185
Prescott Avatar answered Sep 24 '22 20:09

Prescott


You can do it with:

UPDATE mytable SET Total = Pieces * Price;
like image 26
vbence Avatar answered Sep 21 '22 20:09

vbence


I'm assuming this should work. This will actually put it in the column in your database

UPDATE yourTable yt SET yt.Total = (yt.Pieces * yt.Price)

If you want to retrieve the 2 values from the database and put your multiplication in the third column of the result only, then

SELECT yt.Pieces, yt.Price, (yt.Pieces * yt.Price) as 'Total' FROM yourTable yt

will be your friend

like image 29
Nanne Avatar answered Sep 25 '22 20:09

Nanne