title: MySQL CASE Constructor with Having Function
body: <p>Following is the part of my SQL statement for MySQL tables.</p>
  
  <pre><code>function stockSummary($where){
      $q = $this-&gt;db-&gt;query("
      select
      item_id,
      item_name,
      unit_price,
      sub_type_name,
      pg_no,
  
      ( SELECT @stock := sum(ifnull(qty,0)) AS qty FROM store_update_stock_details de2 
      JOIN store_update_stock st2 ON de2.update_stock_id=st2.update_stock_id 
  
      RIGHT JOIN store_item item2 ON de2.item=item2.item_id WHERE de2.status = 1 and 
      st2.transfer_status = 'Approved'  AND tmp.item_id = item2.item_id GROUP BY item2.item_id) as cur_stock,
  
      SUM(purchase) as purchase,
      SUM(issues) as issues
  
      FROM (
      SELECT
  
      item_name,
      item_id,
      unit_price,
      store_sub_type.sub_type_name,
      store_item.pg_no,
  
      CASE WHEN  order_status = 'purchase' THEN  qty else 0  end as purchase , 
      CASE WHEN  order_status ='issue' and transfer_status='Issued' THEN  qty else 0  end as issues , 
      CASE WHEN  store_update_stock.status != 0 and transfer_status !='Pending' and transfer_status !='Recomended' THEN  qty else 0 
      end as balance
  
      from store_update_stock
      join store_update_stock_details on store_update_stock.update_stock_id=store_update_stock_details.update_stock_id
      join store_item on store_update_stock_details.item=store_item.item_id
      join store_sub_type on store_sub_type.sub_type_name=store_item.item_sub_type
  
      where store_update_stock.status=1 and store_item.usr != 'spsso' $where    
      )tmp
  
      group by item_id, unit_price
  
  
      ");
          if ($q-&gt;num_rows() &gt; 0) {
              return $q-&gt;result();
  
          }
      }
  </code></pre>
  
  <p>This part is correctly outs the purchase quantity, issued quantity and the balance of items. After performing transactions balance of some items may be reached to Zero (0). I do not need to show the items reached 0 balances in the output. </p>
  
  <p>How can I change my query to do that (May be applied "Having function") ? Can anyone help ? </p>