列查询的SUM [英] SUM on a column query

问题描述

大家好，



我的查询需要略微更改才能得到不同的结果集。我写了以下查询：

Hello all,

I need a slight change in my query to get me different result set. I have written the following query:

SELECT SUM(P.ITEMAMOUNT) AS AMOUNT, P.COMPANYID AS COMPANY, P.ACCOUNTID AS ACCOUNT, A.ACCOUNTDESC
FROM PRODUCTS  P
JOIN ACCOUNTS A ON P.COMPANY = A.COMPANY AND P.ACCOUNTID = A.ACCOUNTID
WHERE P.ACCOUNTID LIKE '1%'
GROUP BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,7), P.ACCOUNTID, A.ACCOUNTDESC
ORDER BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,7)

此查询返回：

This query returns:

AMOUNT   COMPANY ACCOUNT ACCOUNTDESC
68000 1 100-10-001 DolarAcc
11500 1 100-10-002 EuroAcc
30000 2 100-10-002 EuroAcc
16000 1 100-11-001 DolarAcc
76000 1 100-11-002 EuroAcc
23750 1 100-12-001 DolarAcc
69000 1 100-12-002 EuroAcc
45750 2 100-11-001 DolarAcc
31000 2 100-11-002 EuroAcc

此查询返回账户的结果（金额）*** - ** - ***（例如。 100-11-002）但是

我希望它根据帐户的前7个字母而不是整个帐户和帐户分组返回金额结果的总和。我怎样才能做到这一点？

最终结果应该是：

This query returns the results(sum amounts) for accounts ***-**-*** (for ex. 100-11-002) but
I want it to return the sum of the amounts results based on the first 7 letters of the accounts not the whole and group by company and the account. How can I manage to this?
Final Result should be:

AMOUNT COMPANY ACCOUNT
79500 1 100-10
30000 2 100-10
92000 1 100-11
92750 1 100-12
76750 2 100-11

提前致谢。

Thanks in advance.

推荐答案

试试吧：

Try it:

SELECT SUM(P.ITEMAMOUNT) AS AMOUNT, P.COMPANYID AS COMPANY, SUBSTR(P.ACCOUNTID, 1,5) AS ACCOUNT
FROM PRODUCTS  P INNER JOIN ACCOUNTS A ON P.COMPANY = A.COMPANY AND P.ACCOUNTID = A.ACCOUNTID
WHERE P.ACCOUNTID LIKE '1%'
GROUP BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,5)
ORDER BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,5)

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