Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » sum query trouble

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

Page: 1
Go to End
Author Topic: 400 Views
  • voodoochile
    Avatar
    Community Member
    44 Posts

    sum query trouble Link to this post

    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.

  • Willr
    Avatar
    Forum Moderator
    5464 Posts

    Re: sum query trouble Link to this post

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

  • voodoochile
    Avatar
    Community Member
    44 Posts

    Re: sum query trouble Link to this post

    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

  • voodoochile
    Avatar
    Community Member
    44 Posts

    Re: sum query trouble Link to this post

    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

    400 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.