none
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.


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

Answers

All replies

  • 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

    Monday, November 19, 2012 8:24 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:35 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


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

    Monday, November 19, 2012 8:41 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:42 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:51 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:53 AM
  • yes, and it gives me the error
    Monday, November 19, 2012 8:55 AM
  • 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


    Monday, November 19, 2012 9:15 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 9:37 AM
  • 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 12:09 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;

    Monday, November 19, 2012 1:55 PM