Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Data Model Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

sum query trouble

Go to End

4 Posts   1156 Views


Community Member, 52 Posts

28 September 2012 at 11:20am


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.


Forum Moderator, 5523 Posts

30 September 2012 at 6:23pm

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();


Community Member, 52 Posts

1 October 2012 at 1:23pm

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


Community Member, 52 Posts

2 October 2012 at 1:01pm


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