3062 Posts in 864 Topics by 646 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 233 Views |
-
sum query trouble
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.
-
Re: sum query trouble
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();
-
Re: sum query trouble
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
-
Re: sum query trouble
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
| 233 Views | ||
|
Page:
1
|
Go to Top |
