How to SUM two fields within an SQL query

Question

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row   This is my code    Sum tbl1 fld1   tbl1 fld2  AS  Total    Is this what the SUM function is used for  or can you only use the SUM function for getting the total of a column   Thanks

User · Answer

SUM is used to sum the value in a column for multiple rows  You can just add your columns together   select tblExportVertexCompliance TotalDaysOnIncivek   tblExportVertexCompliance IncivekDaysOtherSource AS  Total Days on Incivek

User · Answer

Due to my reputation points being less than 50 I could not comment on or vote for E Coder s answer above   This is the best way to do it so you don t have to use the group by as I had a similar issue  By doing SUM  coalesce VALUE1  0      coalesce VALUE2  0    as Total this will get you the number you want but also rid you of any error for not performing a Group By  This was my query and gave me a total count and total amount for the each dealer and then gave me a subtotal for Quality and Risky dealer loans   SELECT      DISTINCT STEP1 DEALER NBR      COUNT    AS DLR TOT CNT      SUM  COALESCE DLR QLTY 0    COALESCE DLR RISKY 0    AS DLR TOT AMT      COUNT STEP1 DLR QLTY  AS DLR QLTY CNT      SUM STEP1 DLR QLTY  AS DLR QLTY AMT      COUNT STEP1 DLR RISKY  AS DLR RISKY CNT      SUM STEP1 DLR RISKY  AS DLR RISKY AMT     FROM STEP1     WHERE DLR QLTY IS NOT NULL OR DLR RISKY IS NOT NULL         GROUP BY STEP1 DEALER NBR

User · Answer

SUM is an aggregate function  It will calculate the total for each group    is used for calculating two or more columns in a row   Consider this example   ID  VALUE1  VALUE2                     1   1       2 1   2       2 2   3       4 2   4       5    nbsp   SELECT  ID  SUM VALUE1   SUM VALUE2  FROM    tableName GROUP   BY ID   will result  ID  SUM VALUE1   SUM VALUE2  1   3           4 2   7           9    nbsp   SELECT  ID  VALUE1   VALUE2 FROM    TableName   will result  ID  VALUE1   VALUE2 1   3 1   4 2   7 2   9    nbsp   SELECT  ID  SUM VALUE1   VALUE2  FROM    tableName GROUP   BY ID   will result  ID  SUM VALUE1   VALUE2  1   7 2   16

User · Answer

If you want to add two columns together  all you have to do is add them   Then you will get the sum of those two columns for each row returned by the query   What your code is doing is adding the two columns together and then getting a sum of the sums   That will work  but it might not be what you are attempting to accomplish

User · Answer

Try the following   SELECT     FieldA   FieldB  AS Sum FROM Table

User · Answer

The sum function only gets the total of a column  In order to sum two values from different columns  convert the values to int and add them up using the  -Operator  Select  convert int  col1  convert int  col2   as summed from tbl1   Hope that helps

User · Answer

Just a reminder on adding columns   If one of the values is NULL the total of those columns becomes NULL   Thus why some posters have recommended coalesce with the second parameter being 0  I know this was an older posting but wanted to add this for completeness

User · Answer

ID  VALUE1  VALUE2                     1   1       2  1   2       2 2   3       4 2   4       5  select ID   coalesce VALUE1  0    coalesce VALUE2  0  as Total from TableName

[sql] How to SUM two fields within an SQL query

Examples related to sql

Examples related to ms-access