Sum rows after comparing one row with multiple rows

Question

I have two tables that will be a and b. I want to compare number in a to number in b where they share an ID. a has a single row, while b has multiple. If the sum of b is less than a, then that row of a should be added to the total sum, otherwise it should be skipped.

Table a
ID  Number
4   50
5   60
6   70

Table b
ID  Number  SharedID
1   30      4
2   25      4
3   50      5
4   5       5
5   30      6
6   10      6

Using that example: b 1 and 2 are greater than a 4, so it wouldn't be counted. b 3 and 4 are less than a 5 so it would count. b 5 and 6 are less than a 6, so it would count. The total should be 130.

I'm having trouble with doing a comparison of one row to multiple and then only summing some of the numbers.

Answer 1

This should do the job:

select sum(Number)
from a
where Number > (select sum(Number) from b where b.SharedID = a.ID)

Answer 2

Try this query:

SELECT SUM(a.Number)
FROM a INNER JOIN
(
    SELECT b.SharedID, SUM(b.Number) AS theSum
    FROM b
    GROUP BY b.SharedID
) t
ON a.ID = t.SharedID
WHERE t.theSum < a.Number

The conceptually easiest option is to create a temporary table containing the sums of Table b, and then to JOIN that back to Table a. The WHERE clause restricts your total to only a Number values which are greater than the b sum.