As you can see by the title, I cannot really phrase what I want to do. Basically I have 2 tables:
select sum(offer_price) from offers where offer_id IN
select offer_id from carts where user_name='root'
You should join the tables:
select sum(c.offer_count * o.offer_price) total from carts c join offers o on c.offer_id = c.offer_id where c.user_name = 'root'