title: MySQL two table join - get result according to status - did not work
body: <p>I am trying to get number of pending items SUM by joining two tables, which are delivery and delivery_history. </p>
  
  <p>delivery_history table has status. For example one order can have many status such as Pending, Invoiced.</p>
  
  <p><strong>delivery</strong></p>
  
  <pre><code>id | items
  -----------
  10 | 12
  11 | 10
  13 | 20
  </code></pre>
  
  <p><strong>delivery_history</strong> </p>
  
  <pre><code>id | delivery_id | status 
  ---------------------
  1    10         Pending
  2    10         Invoiced - this is the current status of delivery id 10
  3    11         Pending  - this is the current status of delivery id 11
  4    13         Pending  - this is the current status of delivery id 13
  </code></pre>
  
  <p>I want to get sum of the items of all pending orders. For that I wrote following query but it did not work and gave me an errors. Could someone please look into this. </p>
  
  <blockquote>
    <p>You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as latest_status = "Pending"' at line 12 </p>
  </blockquote>
  
  <pre><code>  SELECT SUM(delivery.items) AS pending_count
      FROM delivery 
      JOIN delivery_history ON delivery_history.delivery_id = delivery.id
      WHERE delivery.deleted="0"  
      AND
      (select dh.delivery_status
      from delivery_history dh
      where dh.delivery_id = delivery.id
      order by dh.id desc
      limit 1
      ) 
      as latest_status = "Pending"
  </code></pre>