how to calculate(add) value of two rows and pass the value to a select statement

Question

Hi to all,

just want to ask if how can i calculate the sum of two row columns from table 2 and pass it to another select statement to table 1 please see below result

Table1

VisaTravelID  empNumber       Status    TotalCost

    1                    00002          Requested     ??

table 2

visatravelID    VisaCost

1                    50

1                    100

1                     200

                    = 350

so on table 1 total cost must be 350?

thanks.

Answer 1

select VisaTravelID  ,empNumber ,sum(visaCost)Totalcost from table1 t1 inner join table2 t2 on t1.VisaTravelID  =t2.VisaTravelID 

group by VisaTravelID  ,empNumber

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

this is not correct, cause what if i have so many columns, how can i groupby right?

do you have any other suggestion?

Answer 3

if not correct.. then you should explain your requirement clearly.. also provide your table structure, expected output..sample input to test the result

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 4

this is not correct, cause what if i have so many columns, how can i groupby right?

do you have any other suggestion?

Try

select VisaTravelID,empNumber,(select sum(VisaCost) from table2 b where b.VisaTravelID=a.VisaTravelID) TotalCost
from table1 a;

---

Many Thanks & Best Regards, Hua Min

Answer 5

it gives me an error

Msg 156, Level 15, State 1, Line 9

Incorrect syntax near the keyword 'from'.

Answer 6

Are you running this?

select VisaTravelID,empNumber,(select sum(VisaCost) from table2 b where b.VisaTravelID=a.VisaTravelID) as TotalCost
from table1 a;

---

Many Thanks & Best Regards, Hua Min

Answer 7

yes, and it gives me the error

Answer 8

Please rerun this

select a.VisaTravelID,empNumber,status,sum(VisaCost) as TotalCost
from table1 a inner join table2 b on b.VisaTravelID=a.VisaTravelID
group by a.VisaTravelID,empNumber,status;

---

Many Thanks & Best Regards, Hua Min

Answer 9

Hi,

Try this.

 SELECT t1.visatravelID,t1.empNumber,t1.Status , SUM(t2.VisaCost) AS TotalCost 
 FROM #table1 t1 INNER JOIN #table2 t2
 ON t1.visatravelID=t2.visatravelID GROUP BY t1.visatravelID,t1.empNumber,t1.Status

Answer 10

If you want to update Table1's Totalcost then

UPDATE T1 SET TOTALCOST=RES.COST FROM TABLE1 T1 INNSER JOIN (SELECT VISATRAVELID,SUM(VISACOST) AS COST FROM 
TABLE 2 GROUP BY VISATRAVELID) RES ON (T1.VISATRAVELID=RES.VISATRAVELID)

Answer 11

Hi, This query below replicates table one with the totalcost filled in from sums on table 2. The LEFT JOIN returns a null for the calculated cost if you don't have any values. The Coalesce uses the calculated values unless null, then it uses the stored table 1 values unless null then 0. Switching to an INNER JOIN returns only those rows with a VisaTravelID group in table 2.

SELECT T1.VisaTravelID, T1.empNumber, T1.[Status], COALESCE(Q2.[TotalCost]
, T1.[TotalCost], 0) FROM T1 LEFT JOIN
(
SELECT [VisaTravelID],SUM([VisaCost]) AS TotalCost
FROM T2 GROUP BY [VisaTravelID]
) Q2
ON T1.VisaTravelID = Q2.VisaTravelID;