MySQL Query to sum data from another table in select query

Question

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

Why "WITHOUT join's"? The syntax you're using includes JOINS, but using the comma syntax. — Tangentially Perpendicular
– Tangentially Perpendicular, Commented Jun 14, 2021 at 23:06
@TangentiallyPerpendicular I removed it from the title, I found them posts here that uses left join but that don't work for me, that's why — Arevan Shamal
– Arevan Shamal, Commented Jun 14, 2021 at 23:39

Mafei · Accepted Answer · 2021-06-14 23:47:55Z

1

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

answered Jun 14, 2021 at 23:47

Mafei

3,9683 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query to sum data from another table in select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related