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.

Data Model Questions /

sum query trouble


Reply


4 Posts   546 Views

Avatar
voodoochile

Community Member, 52 Posts

28 September 2012 at 11:20am

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.

Avatar
Willr

Forum Moderator, 5513 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();

Avatar
voodoochile

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

Avatar
voodoochile

Community Member, 52 Posts

2 October 2012 at 1:01pm

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