Hi

i'm having a little trouble with a query i am writing (MySQL knowledge is limited)

i am trying to get the sum of a result from another query if that makes sense,

i have this query working

$sqlQuery = new SQLQuery( 
		
    	"Recipe.Weight * Product.CostPrice",
     	"Recipe, Product",  
		"Product.ID = " . $this->owner->ProductID
		 );
	 
    	$Price = $sqlQuery->execute()->value();

    return $Price;

so i would now like to sum all the results by Recipe.QuoteID. i have looked at several ways of doing this but not having much luck as im at the edge of my understanding of this stuff.

i think it needs to create an array of results from the above query and then sum them but i'm not sure if this is correct.

Are you using ss3 or 2.4? In the 3.0 ORM you can do something like Recipe::get()->filter(..)->sum("QuoteID"). In 2.4 I think executing another SQL query could be faster than converting the results to an array and counting that.

$sum = DB::query("SELECT SUM(QuoteID) FROM Recipe WHERE ...")->value();

Hi Willr

Thanks for your reply and suggestions, I am building this in 2.4.

Following your suggestion i have played around and got this far

$sum = DB::query("SELECT SUM (Recipe.QuoteID) FROM Recipe WHERE (SELECT Recipe.Weight * Product.CostPrice FROM Recipe, Product)")->value();

this throws an error

 500 Error: "Couldn't run query: SELECT SUM (Recipe.QuoteID) FROM Recipe WHERE SELECT Recipe.Weight * Product.CostPrice FROM Recipe, Product) Subquery returns more than 1 row 

I'm guessing i need something more in either the main query or subquery to allow it to sum all the rows returned by the subquery, not sure what though

Update:

i have played around further and now have the query working as it should

Final Query

$sum = DB::query("SELECT SUM (Recipe.Weight * Product.CostPrice) FROM Recipe, Product WHERE ProductID = Product.ID GROUP BY Recipe.QuoteID")->value();

Thanks for the help in pointing me in the right direction