Arevan Shamal
Arevan Shamal

Reputation: 117

MySQL Query to sum data from another table in select query

enter image description here

I have a hard time calculating the items.sell_price by the bill id.

This is my query.

    select bill.name,sum(item.sell_price) as 'price' 
    from bills,bill_information,items 
    where bills.id_bill = bill_information.bills_id 
      and 
          bill_information.item_id = item.id_item

But the problem is the sum doesn't work the way I want. I want to sum the sell_prices in table items when bills.id_bill has two item_id in the bill_information.

If we have two item_id in bill_information I want it to sum just those two, not all the sell_price in the items table

:( hope you get it

Upvotes: 0

Views: 51

Answers (1)

Mafei
Mafei

Reputation: 3819

what is the table is doesn't matter in this case you have to do only one thing. please group by the bill id like below,

select bill.name,sum(item.sell_price) as 'price' 
from bills,bill_information,items 
where bills.id_bill = bill_information.bills_id 
and 
bill_information.item_id = item.id_item
GROUP BY bills.id_bill

the new part is,

GROUP BY bills.id_bill

Upvotes: 1

Related Questions