3460 Posts in 1064 Topics by 739 members
|Go to End|
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",
"Product.ID = " . $this->owner->ProductID
$Price = $sqlQuery->execute()->value();
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.
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();
1 October 2012 at 1:23pm
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
2 October 2012 at 1:01pm
i have played around further and now have the query working as it should
$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
|Go to Top|