Counting total value of products

Cheule

Hi, I'm new (both to PHP/MySQL and these forums) and I need your help.

I'm trying to use SUM in an SQL select statement to count up the value of all my products in the products database. However, it only adds up each row once, if I have 32 of a certain product it only adds the price of that row up once.

Does anyone know how to get the desired result?

laserlight

I gather that you have a price column and a quantity column in that products table. In that case, you could try something like:

SELECT SUM(price * quantity) AS total_value FROM products

Cheule

Thank you! 🙂

I used the following based on your help:

$total_value = mysql_query("SELECT SUM(price * stock) AS total FROM products WHERE stock >0");

Obviously I used the "WHERE stock >0" argument because I didn't want the query to return the value of stock in instances where I had none. 🙂

I'm really glad I came here today, I'll be sticking around like glue for sure! 😃

laserlight

Obviously I used the "WHERE stock >0" argument because I didn't want the query to return the value of stock in instances where I had none.

On the other hand, if stock=0, then price*stock=0, and adding 0 to the total makes no difference to it 🙂

There might be a difference in efficiency, but stock is not a particularly good choice for an index, so I doubt there would be much of a difference either way.