title: Selecting average total where an associated table has id present
body: <p>I'm fairly new to SQL and I'm trying to answer this question:</p>
  
  <p>What is the average order total, where product X is present.</p>
  
  <p>We have an orders table, and line items. An order has many line items, which again store the <code>product_id</code>.</p>
  
  <pre><code>SELECT
      avg(total) 
  FROM
      orders 
  WHERE 
      (shipment_state = 'shipped')
      AND (delivery_date BETWEEN '2017-09-11' AND '2017-09-18');
  </code></pre>
  
  <p>is what I have now and that is working, however I do not know how to fetch and calculate it based on another table (in this case <code>line_item</code>)</p>