title: SQL (Oracle) query for sales by item
body: <p>I need help to build a ORACLE SQL query that can give me Sales by Item Name for each month.
  Here are the columns that are needed in query – Date (month), Item Name, Quantity Sold, Average Unit Price, Net Sales , Credit units</p>
  <ul>
  <li><p>Quantity Sold</p>
  <p>of an Item is the net quantity sold after credits for a particular item in that month; that is
  =SUM(Invoice Items. Quantity sold) – SUM(Credit Note Items. credit Quantity )</p>
  </li>
  <li><p>Net Sales</p>
  <p>of an item in a particular month  =  SUM(Invoice Items.Total Amount) - SUM(Credit Note Items. Credit Amount)</p>
  </li>
  <li><p>Credit units
  :  it refers to total units of item(credit Quantity) that have been credited in that  month</p>
  </li>
  </ul>
  <p>Below are tables:</p>
  <p><strong>Invoice Items</strong> (Invoice Date, Invoice ID, Product ID, Item Name, Quantity sold, Total Amount)</p>
  <p><strong>Inventory Adjustment</strong> (Date, Entity, EntityID, ItemID, Item Name, Quantity, Unit Price, Stock On Hand, Net Amount)
  This table holds unit price and quantity of an item when invoice or credit note for that item was generated. Entity can be 'Invoice' or 'CreditNote'. EntityID is Invoice ID or CreditNotes ID</p>
  <p><strong>Credit Notes</strong> (CreditNote Date, CreditNotes ID, Customer ID)</p>
  <p><strong>Credit Note Items</strong> (CreditNotes ID, Item ID, Item Name, credit Quantity, Credit Amount)
  Note: this is line item table for Credit Notes table. CreditNote Date is available in master table</p>
  <p>So far, I have this query but I'm unable to figure out next step of joining with credit note and credit note items and getting Net Sales and Quantity Sold.</p>
  <pre><code>SELECT
           inv_items.&quot;Invoice Date&quot; inv_date,
           inv_items.&quot;Product ID&quot; inv_items_Prod_ID,
          inv_items.Item Name,
           AVG(IDR.&quot;Unit Price&quot;),
           SUM(inv_items.&quot;Quantity sold&quot;) total_Qty_Sold,
           SUM(inv_items.&quot;Total Amount&quot;) inv_ItemsSale
  FROM  &quot;Invoice Items&quot; inv_items 
  JOIN &quot;Inventory Adjustment&quot; IDR ON IDR.&quot;EntityID&quot;  = inv_items.&quot;Invoice ID&quot;
       AND    IDR.&quot;ItemID&quot;  = inv_items.&quot;Product ID&quot;
       AND    IDR.&quot;Date&quot;  = inv_items.&quot;Invoice Date&quot;  
  GROUP BY 1,
       2,
       3
  </code></pre>
  <p>Any help is much appreciated.</p>