Clever Geek Handbook

Sum lines after comparing one line to multiple lines

I have two tables to be a

and b

. I want to compare number

in a

with number

in b

where they have an id. a

has one line and b

several. If the amount is b

less than a

, then this line from a

should be added to the total amount, 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 this example: b 1 and 2 are greater than 4, so it will not count. b 3 and 4 are less than 5, so it will count. b 5 and 6 are less than 6, so it will count. The amount should be 130.

I am having trouble doing a one-to-several string comparison and then summing some numbers.

+3


source to share


3 answers


This should do the job:



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


source


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


Conceptually, the simplest option is to create a temporary table containing the sums of table b and then to JOIN

, which are returned to table a. The proposal WHERE

limits your total to only values Number

greater than the sum of b.

+2


source 


select SUM(number) from(
select case when(aa.number>(select SUM(bb.number)
 from bb where bb.sharedid=aa.id))then aa.number else 0 end as number  from aa )abc

This should do the trick

0


source






More articles:


All Articles