MySQL Sum Problem

I have table a and b.

• a has order_id and ship_total
• b has order_id, item, qty, and price 开发者_JAVA百科

How do I query and get the sum of all ship_total on all orders that have a total qty under 100?

I am afraid if I do something like this, it's going to be wrong.

select SUM(ship_total) FROM a as a inner join b as b on a.order_id=b.order_id WHERE SUM(qty) < 100 GROUP BY a.order_id

Any help would be greatly appreciated!

I've tried the answers from below and it's not adding everything together. It's giving me the shipping amount from each order.

One way:

SELECT
    SUM(A.ship_total)
FROM
    (SELECT B.order_id FROM B GROUP BY B.order_id HAVING SUM(qty) < 100) SQ
INNER JOIN A ON
    A.order_id = SQ.order_id

Another way:

SELECT
    SUM(A.ship_total)
FROM
    A
WHERE
    order_id IN (
        SELECT order_id
        FROM B
        GROUP BY order_id
        HAVING SUM(qty) < 100)

Try each out and see which performs best for you.

You're close. When you want to limit results that are aggregated, you need to use the HAVING clause.

select SUM(ship_total) 
FROM a as a 
inner join b as b 
  on a.order_id=b.order_id 
GROUP BY a.order_id
having SUM(qty) < 100