CODEWITHSUNDEEP
sql
BinaryDuck
BinaryDuck

Reputation: 82

subquery the same table in select statement

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.

Upvotes: 3

Views: 45176

Answers (3)

roman
roman

Reputation: 117636

Hard to say, but looks like you need recursive cte. Here's example for PostgreSQL:

with recursive cte as (
    select
        t.invoice_num, t.price, t.item_id, t.item_num
    from Table1 as t
    where t.parent_item_id is null
    union all
    select
        t.invoice_num, t.price, t.item_id, c.item_num
    from Table1 as t
        inner join cte as c on c.item_id = t.parent_item_id
)
select invoice_num, item_num, sum(price)
from cte
group by invoice_num, item_num

sql fiddle demo

I've used null for empty parent_item_id (it's better solution than using empty strings), but you can change this to ''.

Upvotes: 0

dcp
dcp

Reputation: 55467

 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.

Upvotes: 4

Hogan
Hogan

Reputation: 70528

Not clear based on your question (see my comment) but as I understand it a simple group by will give you what you want. If not please explain (in the original question) why does this query does not work --- what is it missing from your requirements?

SELECT item_num, SUM(price) 
FROM invoices
GROUP BY item_num

Upvotes: 1

Related Questions