[SOLVED] multiply 2 columns and add them

[guyfromfl]

ok lets say I have a table

 

--------------------------------
| id  | invoiceId |  qty  |  price  |
--------------------------------
|1    | 10354     | 2      | 5       |
|2    | 10356     | 3      | 6       |
|3    | 10354     | 4      | 1       |
--------------------------------

 

I want to multiply qty * price where invoiceId=10354 then add the sum of each.

 

would it be better to just add a total column? or is there a sql function that i could use?

 

thanks

[smerny]

what do you want to do with the information? just output it?

 

use the query SELECT qty, price WHERE invoiceID='10354' and store it to $row or something.... then

 

$total = $row['qty'] * $row['price'];

[guyfromfl]

Yea im making an invoice system in php.

 

I have 2 tables parts.invoice (customer data record of the invoice....this has a total field)

and  parts.invoiceitems (individual things a customer buys.)

 

so if I add a new item after the invoice is generated, I want to have a new total to put in parts.invoice.total.

 

and when I add an item to the order, I have to update the invoice total.

 

I have php creating the subtotals for the invoice, but I was tinkering with the idea of doing it all on the database.

 

Any suggestions?

[Philip]

I'm not sure what you want to add... all of the multiplication products?

here's for multiplying:

SELECT (qty * price) as total FROM table WHERE id = 10354

 

You can use SUM to get the total.... but I'd do that in PHP

[guyfromfl]

basically multiply qty and price from row 1, then mulitply qty and price from row 3 then add it together.

 

My script is already doing it in php, I just wanted to see how it works if I get it from the db and leave it all in the mysql database.

[Philip]

If you just want the sum, and not data from each row:

SELECT SUM(qty * price) as total FROM table WHERE id = 10354

[guyfromfl]

Yea thats it,

 

I dont know why I was getting an error when i was trying that before, i think its because I didnt tell it what to alias it as maybe?

 

I dunno but thats what I was looking for

 

Thanks!