title: How to build a Laravel query with joins and sum?
body: <p>I am trying to configure a query within a Laravel app that is equivalent to this:</p>
  
  <pre><code>SELECT SUM(balance), name FROM db.statement_versions
  INNER JOIN statements ON statement_versions.statement_id = statements.id
  INNER JOIN accounts ON statements.account_id = accounts.id
  GROUP BY name;
  </code></pre>
  
  <p>This query works when I run it in MySQL Workbench, but when I try to translate it into PHP with the Laravel query builder I am getting an error. What I ultimately want is to return all <code>accounts</code> with their summed balance of <code>statement_versions.balance</code>. Here is my code right now:</p>
  
  <pre><code>public static function query(LensRequest $request, $query)
  {
      return $request-&gt;withOrdering($request-&gt;withFilters(
          $query-&gt;select('accounts.name')-&gt;sum('statement_versions.balance')
              -&gt;join('statements', 'statement_versions.statement_id', '=', 'statements.id')
              -&gt;join('accounts', 'statements.account_id', '=', 'accounts.id')
              -&gt;orderBy('balance', 'desc')
              -&gt;groupBy('statement_versions.balance', 'accounts.name')
      ));
  }
  </code></pre>
  
  <p>I have tried a couple different variations of this, but I get the error <code>SQLSTATE[42S22]: Column not found: 1054 Unknown column 'statement_versions.balance' in 'field list'</code>. How can I solve this and get the query working correctly?</p>