在SQL中汇总三列 [英] Summing Three Columns in SQL

问题描述

好的，我需要一些帮助.我有四个包含数值的列，我想对其求和并将第五列合计为所有四个列.我尝试使用以下代码，但未成功:SUM(JC_COST.COST_AMNT + JC_COST.COST_BRDN_AMNT1 + JC_COST.COST_BRDN_AMNT2 + JC_COST.COST_BRDN_AMNT3) AS TotalCost,

不知道我在做什么错.这是我没有sum列的代码:

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:

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!

推荐答案

SUM()是一个聚合函数，这意味着您只能将其与group by和一起使用.它会汇总特定列中的所有值.


就您而言，我认为不需要SUM.只需键入

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

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

这是因为您需要将一列的所有值汇总到第四列，并对每一行重复此操作.

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

(通过您的查询和问题，这是我可以获得的理解.)

*如果解决，请标记为答案.

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.

这篇关于在SQL中汇总三列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！