Summing Three Columns in SQL

Question

0.00/5 (No votes)

See more:

Ok, I am in need of some assistance. I have four columns containing numeric values that I would like to sum and make a fifth column totaling all four columns. I tried to use the following code and was unsuccessful: SUM(JC_COST.COST_AMNT + JC_COST.COST_BRDN_AMNT1 + JC_COST.COST_BRDN_AMNT2 + JC_COST.COST_BRDN_AMNT3) AS TotalCost,

Not sure what I am doing incorrectly. Here is my code without the sum column:

SQL

SELECT     JC_COST.COST_JOB_NO, JC_COST.COST_DATE, JC_COST.COST_DESC,  JC_COST.COST_EMP, JC_COST.COST_VEND_NO, JC_COST.COST_AP_VOUCHER_NO,
                      JC_COST.COST_AP_INV_NO, JC_COST.COST_CLASS, JC_CLASS.CLASS_DESC, JC_COST.COST_ELEMENT, JC_COST.COST_SOURCE_CODE, JC_CELM.CELM_DESC,
                      JC_COST.COST_AMNT, JC_COST.COST_BRDN_AMNT1, JC_COST.COST_BRDN_AMNT2, JC_COST.COST_BRDN_AMNT3, JC_COST.COST_EARN_CODE,
                      JC_COST.COST_HRS_QUANTITY, JC_COST.COST_CNCT_LAB_CAT, JC_COST.COST_AMNT_BILLED, JC_COST.COST_BRDN_BILLED1,
                      JC_COST.COST_BRDN_BILLED2, JC_COST.COST_BRDN_BILLED3, JC_COST.COST_QTY_BILLED, JC_COST.COST_BILL_DATE, JC_COST.COST_BILLED_FLAG,
                      JC_COST.COST_INV_NO, JC_COST.COST_AMNT_REV, JC_COST.COST_BRDN_REV1, JC_COST.COST_BRDN_REV2, JC_COST.COST_BRDN_REV3,
                      JC_COST.COST_REV_FLAG, JC_COST.COST_REV_DATE, JC_COST.COST_GL_ACCT, MAFG_ResourceRateTypeCategories.Category,
                      SUBSTRING(JC_COST.COST_JOB_NO, 1, 2) AS CLIN, SUBSTRING(JC_COST.COST_JOB_NO, 3, 6) AS WBS, SUBSTRING(JC_COST.COST_JOB_NO, 9, 3) AS TaskOrder,
                      SUBSTRING(JC_COST.COST_JOB_NO, 12, 3) AS Fac, SUBSTRING(JC_COST.COST_JOB_NO, 15, 3) AS USR, AccountingPeriods.AcctPrd_Name

FROM         MAFG_ResourceRateTypes INNER JOIN
                      MAFG_ResourceRateTypeCategories ON MAFG_ResourceRateTypes.CategoryId = MAFG_ResourceRateTypeCategories.CategoryId INNER JOIN
                      ResourceRateTypes ON MAFG_ResourceRateTypes.ResRateType_ID = ResourceRateTypes.ResRateType_ID RIGHT OUTER JOIN
                      JC_COST INNER JOIN
                      JC_CELM ON JC_COST.COST_ELEMENT = JC_CELM.CELM_CODE INNER JOIN
                      JC_CLASS ON JC_COST.COST_CLASS = JC_CLASS.CLASS_CODE ON ResourceRateTypes.ResRateType_Name = JC_COST.COST_CNCT_LAB_CAT INNER JOIN
                      AccountingPeriods ON JC_COST.COST_DATE >= AccountingPeriods.AcctPrd_StartDate AND JC_COST.COST_DATE <= AccountingPeriods.AcctPrd_EndDate

WHERE     (JC_CELM.CELM_TABLE_CODE = 'DIRECT') AND (JC_COST.COST_DATE >= @Param1) AND (JC_COST.COST_DATE <= @Param2)

ORDER BY JC_COST.COST_DATE, JC_COST.COST_JOB_NO

Help please! Thanks!

Posted 7-Mar-12 10:48am

NeNe4877

Updated 7-Mar-12 11:50am

thatraja

v2

Add a Solution

Comments

ZurdoDev 7-Mar-12 16:54pm

Ouch, my eyes hurt. That is a lot of code. Anyway, what does happen? You say it doesn't work but what does happen? Note, if any of those columns have NULLS everything will come back null. You need to either IsNull it or COAELESCE your values before you add them.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jyothikarthik_N · Accepted Answer · 2012-03-07T17:59:00

Hi,

SUM() is an aggregate function, meaning you can use it with only group by and It sums up all the values in a particular column.

In your case, I believe SUM is not required. Simply type

SQL

SELECT JC_COST.COST_AMNT + JC_COST.COST_BRDN_AMNT1 + JC_COST.COST_BRDN_AMNT2 + JC_COST.COST_BRDN_AMNT3 AS TOTALCOST FROM Table

This is because you need to sum up all values of a column into a fourth column, repeating this for each row.

Col1/Col2/Col3/TotalCost
1/2/3/6
2/1/5/8

(From your query and question, this was the understanding I could get.)

*Mark as answer if this solves.