[SOLVED] Totalling a generated column

[TechMistress]

Hello, I'm just teaching myself MySQL, and I was able to get results from several queries on several tables nicely.

 

Now I want to total up one of the columns.

 

I've queries 2 tables to get a list of clients, their ids, the name of the item they ordered, the price of the item, etc.

 

How do I total up the prices that resulted "above" in the column?

 

To get the price in the first place to appear on the table, I queried for clients who are already processed - and the query looked like this:

 

$result = mysql_fetch_array(mysql_query("SELECT name,price FROM product WHERE id='$sales2[purchase]'"));

$result2 = mysql_fetch_array(mysql_query("SELECT name FROM clients WHERE id='$sales2[client]'"));

 

Then the price appears in the table as $result[price]

 

The sales query is further above in the code.

 

Thank you!

[Wildbug]

Two options.  You can add up the prices while you process your query in PHP, or you can use the SUM() function to get...wait for it... a sum.  You'll have to do the SUM in a stand alone query.

[TechMistress]

Well, I figured I could use SUM, but I wasn't sure what to put in the query to sum up one particular column. I'm guessing there isn't a sum "above" - and the column doesn't have a name.

 

I'm guessing it has to do something with doing a SUM on the results of the previous query?

[TechMistress]

Ok, I tried this:

 

$sale = mysql_query("SELECT * FROM op_sales WHERE status='processed'");
$total = mysql_query("SELECT SUM(price) FROM op_product WHERE id='$sale[purchase]'");

 

then I echoed $total

 

But I get a Resource id#21

 

Any help?

[akitchin]

the reason you're getting a resource ID is because mysql_query() only runs the query in the database and returns a result set resource - you then need to use a fetching function to process that resource into its results.

 

regardless, you can take care of this in one fell swoop:

 

$total = mysql_query("SELECT SUM(price) FROM op_product WHERE status='processed'");

[TechMistress]

Ok, but the problem is, the status is in another table called sales - the price is in the product table - but they share a common id.

[akitchin]

then this calls for a join:

 

SELECT SUM(product.price) as totalcost FROM product INNER JOIN sales ON product.purchase=sales.purchase WHERE sales.statud='processed'

[TechMistress]

Ok, almost there, I think.

 

I took what you had, and changed one item - since the product id matches the sales purchase:

 

$total = mysql_query("SELECT SUM(op_product.price) as totalcost FROM op_product INNER JOIN op_sales ON op_product.id=op_sales.purchase WHERE op_sales.status='processed'");

 

Then I echo $total.

 

Now I get a Resource ID#20.

 

I'm so sorry - I'll get it eventually...

[akitchin]

don't apologize, everybody's gotta learn sometime, right?  mysql_query() returns a resource ID, which you must then use a fetching function such as mysql_fetch_array(), mysql_result(), mysql_fetch_row(), etc.:

 

$total = mysql_query("SELECT SUM(op_product.price) as totalcost FROM op_product INNER JOIN op_sales ON op_product.id=op_sales.purchase WHERE op_sales.status='processed'");
$actual_total = mysql_result($total, 0, 0);
echo $actual_total;

[TechMistress]

Ahaaa,

 

Now I get it.  I have been mimicking code for a while to understand it - and I finally got to a place where I understood - but then you told me what to do with it.  Thanks!  It's so much easier to actually understand what I'm doing, rather than just copying.

 

Yippee.