SQL - Reference current row

Question

Hello I have the following tables :

TableA

ID | SomeInt
1     55
1     66
2     77

TableB

ID | OtherInt
1    ComputedBy Field
2    ComputedBy Field

The computed by field needs to return the sum from tableA where TableB.ID = TableA.ID, but if I say:

SELECT SUM(SomeInt) from TableA where ID = TableA.Id

where the first ID would be the current table's ID, the dbms says "Ambiguous field name between table Table1 and Table2"

So, how do I get the ID from the current row so that it doesnt mix the field name.

Something like This.ID, Self.ID, CurrentRow.ID or whatever sql uses

EDIT: Apparently I could use Current table's name to reference the column

Answer 1

SELECT B.ID, SUM(A.SomeInt) AS Total 
From TableA A, TableB B where A.ID = B.Id
GROUP BY B.ID

Answer 2

Simple answer

select tb.id
      ,sum(ta.someint)
  from TableA ta
       inner join TableB tb
               on ta.id = tb.id
group by tb.id

This is a fully worked out example of your problem excuse tsql syntax.

declare @TableA table(id int
                         ,someint int)

insert @TableA values(1,55)
insert @TableA values(1,66)
insert @TableA values(2,77)

declare @TableB table(id int)

insert @TableB values(1)

select tb.id
      ,sum(ta.someint)
  from @TableA ta
       inner join @TableB tb
               on ta.id = tb.id
group by tb.id

Answer 3

 SELECT TableB.ID, SUM(TableA.SomeInt) 
    FROM TableB 
        LEFT OUTER JOIN TableA ON
            TableB.ID = TableA.ID
    GROUP BY TableB.ID

Answer 4

Are you trying to make TableB have the sum of TableA's values? Do the ID's already exist (just need to be updated) or do you need to populate TableB from scratch? I don't understand what your overall goal is, and that would affect the answer.

E.G.

• Update w/ group by
• Insert
• Trigger that updates TableB based on changes to tableA
• Simple select...

Let me know what your goal is and we can get an ideal answer.

Answer 5

You might simply make "TableB" itself a computed view:

CREATE VIEW TableB AS SELECT ID, SUM(SomeInt) AS OtherInt FROM TableA;

SELECT * FROM TableB;
 id | otherint 
----+----------
  2 |       77
  1 |      121
(2 rows)