SQL Query To Sum Two Columns Values Based on Its Record


  • May i know what is the SQL query to create another intermediate column which is used to store the sum of two columns of each and every record. Thank you.
    Tuesday, March 27, 2007 5:02 AM


All replies

  • Maybe this is not what you want, can you please describe more about your requirements?

    SELECT column1, column2, (column1+column2) AS SumColumn FROM Table1


    Tuesday, March 27, 2007 5:22 AM
  • Thanks for your reply, it is what i want but the output is slightly different from my expectation. cuz the new column called SumColumn  displayed the sums of two zero values as 00 and non zero values as 03.4, may i know how to get rid of the first zero. Thank you.
    Wednesday, March 28, 2007 2:37 AM
  • Are the columns strings? If so, you need to cast them to numbers before adding them.
    Wednesday, March 28, 2007 3:43 AM
  • you can convert to decimal like this:

    CONVERT(DECIMAL(9, 2), Column1+Column2) AS SumColumn




    Please mark the post as answer.

    Wednesday, March 28, 2007 5:49 AM
  • Or you can format the field in your report like this:

    Format(Fields!SumColumn.Value, "#.0")



    Thursday, March 29, 2007 12:10 PM
  • Thanks for solution for sum of two column.


    Friday, April 30, 2010 7:43 AM
  • Thanks for sum i used it like this may be helpfull to other fellows as well.


    SELECT  distinct(dbo.table1.ProductNum),

    (cast(Column1 as int) + cast(Column2 as int) + cast(Column3 as int) + cast(Column4 as int) + cast(Column5 as int) + cast(Column6 as int) + cast(Column7 as int) + cast(Column8 as int))
     as sumofcolumns

    from Table2 Inner join  dbo.Table1 on dbo.Table2.ProductNum=dbo.Table1.ProductNum

    where (dbo.Table2.Datestamp between CONVERT(datetime, '2010-09-01 06:00:00',102)and CONVERT(datetime, '2010-09-01 18:00:00',102))




    Thursday, November 11, 2010 9:36 AM
  • in ms sql if want to create space between columns +' '+ sorry for missing before Eric
    Wednesday, December 01, 2010 9:35 AM
  • I have a 3 columns namely int1, int2, results 

    i want to add int1 and int2 and answer will be in results column by setting default constraint for results

    my query is

    create table defaul(int1 int,int2 int,results int default(sum(int1+int2)))

    it's not working

    it's possible or not

    if possible please post yours query 

    thanks in advance

    Wednesday, November 09, 2011 4:30 PM


    Tuesday, December 06, 2011 8:37 AM
  • dont shout :) try this one. you did't specify clearly your problem. I hope this will help you, In table design view add a new column that will hold the sum of two other columns, then under column properties > Computed Column Specification add this ([Column1] + [Column2])... i hope that will help..
    • Edited by coder386 Sunday, December 25, 2011 12:27 AM
    Sunday, December 25, 2011 12:26 AM
  • SELECT     SUM(salesinfo.quantity + salesinfo.quantity1) AS HeadSet,SUM(salesinfo.quantity2 + salesinfo.quantity3) AS BaseUnit, comp_general.comp_name
    FROM         salesinfo LEFT OUTER JOIN
                          comp_general ON salesinfo.si_compid = comp_general.comp_id LEFT OUTER JOIN
                          accountinfo ON salesinfo.si_compid = accountinfo.acin_compid
    WHERE     (accountinfo.innovacustomer = 'yes') AND (accountinfo.categoryid <> 67)
    GROUP BY accountinfo.acin_compid, comp_general.comp_name



    >> use your table and your column names... it will help you

    Tuesday, January 17, 2012 6:56 AM
  • beautifull answer boss, it worked like a breeze...........  keep posting

    thanks a lot


    Tuesday, March 27, 2012 10:38 AM
  • Hello can you please help with this simple easy question?

    I have a table called Answers and I have created a column for each answer like:

    Table name: Answers

    Columns: ID, Name, Answer1, Answer2 .... etc

    My question is how to tell Access 2007 that I want it to sum Answer1 till AnswerN ??

    I read some comments when I was looking for the answer and found 2 ways which are:

    Defining a new column and store the sum of these all answers (Which I prefere)

    and the other one is to show it in the report through query.

    But still need someone professional to tell me how to do it in a simple way since am still a beginner :)

    Thanks a lot for any one who help in this

    Monday, August 26, 2013 9:23 AM