title: How can (SUM) in pivot table field and searching that field in yajra-laravel-datatable package (laravel 5.6)
body: <p>! have three table</p>
  
  <ol>
  <li><strong>inventories</strong>
  <a href="https://i.stack.imgur.com/YPupB.png" rel="nofollow noreferrer">enter image description here</a></li>
  <li><strong>warehouses</strong>
  <a href="https://i.stack.imgur.com/Db2Ca.png" rel="nofollow noreferrer">enter image description here</a></li>
  <li><strong>inventory_has_warehouses</strong>
  <a href="https://i.stack.imgur.com/fexty.png" rel="nofollow noreferrer">enter image description here</a></li>
  </ol>
  
  <p>I have use <code>laravel yajra datatable</code>. i need sum and search of <strong>starting_balance</strong> this field in <strong>inventory_has_warehouses</strong> pivot table</p>
  
  <p>my code:</p>
  
  <pre><code>    $id = Auth::user()-&gt;id;
  
          $row = Inventory::with('contact')-&gt;with('warehouse')
          -&gt;select(
            'inventories.*',
            DB::raw('SUM(inventory_has_warehouses.starting_balance) as total') 
          )
          -&gt;leftJoin('inventory_has_warehouses', 'inventory_has_warehouses.inventory_id', '=', 'inventories.id')
          -&gt;leftJoin('warehouses', 'warehouses.id', '=', 'inventory_has_warehouses.warehouse_id')
          -&gt;where('inventories.subscriber_id',$id)
          -&gt;groupBy('inventories.id');
  
          $datatable = DataTables::of($row)
  
          -&gt;filterColumn('total', function($query, $keyword) {
              $query-&gt;whereRaw('sum(inventory_has_warehouses.starting_balance) like ?', ['%'.$keyword.'%']);
  
          })
  
          return $datatable-&gt;make(true);
  </code></pre>
  
  <p>but i fount this type of error</p>
  
  <blockquote>
    <p>Exception Message:↵↵SQLSTATE[HY000]: General error: 1111 Invalid use
    of group function (SQL: select count(<em>) as aggregate from (select
    <code>inventories</code>.</em>, SUM(inventory_has_warehouses.starting_balance) as
    total from <code>inventories</code> left join <code>inventory_has_warehouses</code> on
    <code>inventory_has_warehouses</code>.<code>inventory_id</code> = <code>inventories</code>.<code>id</code> left
    join <code>warehouses</code> on <code>warehouses</code>.<code>id</code> =
    <code>inventory_has_warehouses</code>.<code>warehouse_id</code> where
    <code>inventories</code>.<code>subscriber_id</code> = 2 and <code>inventories</code>.<code>status</code> = 1 and
    (LOWER(<code>inventories</code>.<code>itemcode</code>) LIKE %1% or
    LOWER(<code>inventories</code>.<code>purchasedescription</code>) LIKE %1% or exists (select
    * from <code>contacts</code> where <code>inventories</code>.<code>supplier</code> = <code>contacts</code>.<code>id</code> and LOWER(<code>contacts</code>.<code>name</code>) LIKE %1%) or
    (sum(inventory_has_warehouses.starting_balance) like %1%)) group by
    <code>inventories</code>.<code>id</code>) count_row_table)</p>
  </blockquote>
  
  <p><strong>mysql query</strong></p>
  
  <blockquote>
    <p>select  inventories., SUM(inventory_has_warehouses.starting_balance)
    as total from inventories left join inventory_has_warehouses on 
    inventory_has_warehouses.inventory_id = inventories.id left join
    warehouses on warehouses.id =  inventory_has_warehouses.warehouse_id
    where inventories.subscriber_id = 2 and inventories.status = 1 and
    (LOWER(inventories.itemcode) LIKE %1% or
    LOWER(inventories.purchasedescription) LIKE %1% or exists (select *
    from contacts where inventories.supplier = contacts.id and
    LOWER(contacts.name) LIKE %1%) or
    (sum(inventory_has_warehouses.starting_balance) like %1%)) group by 
    inventories.id</p>
  </blockquote>