Addi Addi - 1 year ago 114
SQL Question

SQL Multiplying result of subquery with other

As you can see by the title, I cannot really phrase what I want to do. Basically I have 2 tables:

offer_id varchar
offer_price int

user_name varchar
offer_id varchar
offer_count int

Now I want to get the total price of all the items in the shopping basket (cart). I've come this far:

select sum(offer_price) from offers where offer_id IN
select offer_id from carts where user_name='root'

However that approach completely ignores the offer_count value of the shopping basket. How would I implement that? (I'm relatively unexperienced in SQL)

Answer Source

You should join the tables:

    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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download