subquery the same table in select statement

Question

I have a resturant db and I need to total up the total value of all the items sold individually. So if I sold a hamburger that has a base price of $10.00 with bacon which costs $1.00 and a hambuger(again $10.00) with avacado that costs $0.50 I need to get $21.50 returned. My invoice table looks like this:

invoice_num item_num  price   item_id   parent_item_id
111         hmbg      10.00   guid_1    ''
111         bacn      1.00    guid_2    guid_2
112         hmbg      10.00   guid_3    ''
112         avcd      0.50    guid_4    guid_3

I can get the sum of all the parent items like this:

SELECT item_num, SUM(price) FROM invoices WHERE parent_item_id = ''

it is the adding of the toppings that is confusing me. I feel like I need to add a subquery in the SUM but I'm not sure how to go about doing it and referencing the original query to use the item_id.

Answer 1

 SELECT item_num, sum(i.price) + sum(nvl(x.ingred_price,0))
  FROM invoices i
LEFT OUTER JOIN
     (SELECT parent_item_id
             , sum(price) ingred_price
          FROM invoices
         WHERE parent_item_id IS NOT NULL
       GROUP BY parent_item_id) x
ON x.parent_item_id = i.item_id
WHERE i.parent_item_id IS NULL      
GROUP BY item_num

Here's a SQL Fiddle that proves the above code works. I used Oracle, but you should be able to adapt it to whatever DB you are using.

Assumption: You don't have more than one level in a parent child relationship. E.g. A can have a child B, but B won't have any other children.