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

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

• Monday, November 19, 2012 8:21 AM

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.

• Edited by Monday, November 19, 2012 8:41 AM
•

### All Replies

• Monday, November 19, 2012 8:24 AM

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

group by VisaTravelID  ,empNumber

vt

• Monday, November 19, 2012 8:35 AM

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

do you have any other suggestion?

• Monday, November 19, 2012 8:41 AM

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

vt

• Monday, November 19, 2012 8:42 AM

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

• Monday, November 19, 2012 8:51 AM

it gives me an error

Msg 156, Level 15, State 1, Line 9

Incorrect syntax near the keyword 'from'.

• Monday, November 19, 2012 8:53 AM

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

• Monday, November 19, 2012 8:55 AM

yes, and it gives me the error
• Monday, November 19, 2012 9:15 AM

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

• Monday, November 19, 2012 9:37 AM

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```

• Monday, November 19, 2012 12:09 PM

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)```

• Monday, November 19, 2012 1:55 PM

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;```