I have a typical invoice table and a corresponding invoice lines table.
Certain invoices have invoice lines for a specific list of item numbers that I am interested in.
When an invoice has a line item with one of the items from the list, I want to get the total unit sales for that entire invoice including all lines, not just the line with the item_no from my list.
The result set I am looking for would be grouped by this list of items that I interested in.
In the example data, if the list of items I am interested in is (1,4) then the result set would be:
Because ITEM_NO 1 appears in tickets where TICKET_NO = 1 AND TICKET_NO = 2, and the sum of all UNIT_SALES for these 2 tickets is 14.
Because ITEM_NO 4 appears in tickets where TICKET_NO = 3 AND TICKET_NO = 4, and the sum of all UNIT_SALES for these 2 tickets is 24.
Thanks in advance for you help on this.