如何求两个cast列和group by的和

643ylb08 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(315)

我正在尝试生成一个余额查询。我有以下疑问：
我想求functamt1和functamt2的和(我不得不使用cast函数，因为数据是varchar，我需要转换成数字）来解决
然后我想按帐号分组，这样每个帐号就有一个记录。但当我把它归为一组时，它会把错误对准我(在下面的查询中，我仍然看到每个帐户有两个多个记录，而不是总余额。
非常感谢您的建议/支持。谢谢你帮我解决第一点。

SELECT
[Company_number],
SC.[DESCRIPTION],
[account_number],
[account_description],
CAST([ACTL_FUNC_AMT] AS DEC(12,2)) + CAST([ACTL_BGNNG_MO_FUNC_AMT] AS      DEC(12,2)) AS FunctAmt
FROM [balances]
JOIN [company_details] sc
   ON [company_number] = [company_number]
WHERE [company_number] = '132' AND NOT CAST([ACTL_FUNC_AMT] AS DEC(12,2)) = 0 AND      NOT CAST([ACTL_BGNNG_MO_FUNC_AMT] AS DEC(12,2)) = 0
GROUP BY [company_number],
SC.[DESCRIPTION],
[account_number],
[account_description],
   [ACTL_FUNC_AMT],
   [ACTL_BGNNG_MO_FUNC_AMT]

订货人[账号]

sql sql-server

来源：https://stackoverflow.com/questions/61942025/how-do-i-sum-two-cast-columns-and-group-by

1条答案

按热度按时间

eqoofvh91#

不知道你尝试了什么，这有点难说，但我猜也许你只是增加了一个 group by account_number ? 您需要添加不属于聚合的其余列。这就是你要找的吗？

SELECT
    [Company_Number],
    T2.[Company Description],
    [Account_Number],
    [Account_description],
    SUM(CAST([ACTL_FUNC_AMT] AS DEC(12,2)) + CAST([ACTL_BGNNG_MO_FUNC_AMT] AS DEC(12,2))) AS FunctAmt
FROM [general_ledger_data] 
JOIN [company_details] t2
    ON [Company_Number] = [Company_Number1]
WHERE [Company_Number] = 'Comp1'
GROUP BY [Company_Number],
    T2.[Company Description],
    [Account_Number],
    [Account_description]
ORDER BY [Account_Number]

赞(0）回复(0）举报 2021-08-13

我来回答

如何求两个cast列和group by的和

1条答案

相关问题

热门标签

最新问答