12,065,756 members (31,414 online)
Rate this:
See more:
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
```
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
```
Posted 17-Apr-13 22:00pm
Edited 17-Apr-13 22:02pm
Maciej Los213.7K
v3

Rate this:

## Solution 1

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)```
Aarti Meswania 18-Apr-13 3:16am

5+! :)
Maciej Los 18-Apr-13 3:20am

Thank you, Aarti ;)
Aarti Meswania 18-Apr-13 3:24am

Welcome.
Maciej Los 18-Apr-13 4:31am

I found it on Wikipedia[^]: Aarti (also spelt Arati, Arthi, Aarthi, Aarthy, Aarti or Arti) is also a name for Indian women.
Till this moment i've been almost sure that Aarti is men's name...
Nice to meet you, Aarti ;)
Aarti Meswania 18-Apr-13 4:34am

lol
thanks. nice to meet u too
wonder-FOOL 18-Apr-13 3:17am

Hello Maciej thank you very much for your fast response.
Maciej Los 18-Apr-13 3:20am

You're welcome, call again ;)
wonder-FOOL 18-Apr-13 4:20am

HEre I call again :) If I want to AccountDesc field how should I update the sql query? Because when I changed the query to:
SELECT SUM(P.ITEMAMOUNT) AS AMOUNT, P.COMPANYID AS COMPANY, SUBSTR(P.ACCOUNTID, 1,5) AS ACCOUNT, A.ACCOUNTDESC
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), A.ACCOUNTDESC
ORDER BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,5)

I have returning results with the sameaccountid but different account description due to
last 3 digits. Like
100-10 DolarAcc (100-10-001), 100-10 EuroAcc (100-10-002)
But in the Accounts table I have a description for 100-10 as Account For ABC Company. I don't know if I am clear but if you can edit the query such that it will be awesome thank you very much for your help.

To be more clear in that Accounts table
100-10 stand for Accounts ABC Company
100-10-001 = Dolar Acc For ABC company
100-10-002 = Euro Acc For ABC company

so in the querys result breakdown can we generalize the accountdes column to Accounts ABC Company like we did on the Accounts column?
Maciej Los 18-Apr-13 4:38am

If i understand you well, you want to add another column, but doing it, query returns bad result set, yes?
Try to do:
```SEELCT T.*, B.ACCOUNTDESC FROM( SELECT SUM(P.ITEMAMOUNT) AS AMOUNT, P.COMPANYID AS COMPANY, SUBSTR(P.ACCOUNTID, 1,5) AS ACCOUNT FROM PRODUCTS P INNER JOIN ACCOUNTS AS 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), A.ACCOUNTDESC ORDER BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,5) ) AS T INNER JOIN JOIN ACCOUNTS AS B ON T.ITEMAMOUNT = B.AMOUNT```
wonder-FOOL 18-Apr-13 4:39am

SELECT SUM(P.ITEMAMOUNT) AS AMOUNT, P.COMPANYID AS COMPANY, SUBSTR(P.ACCOUNTID, 1,5) AS ACCOUNT, (SELECT ACC.ACCOUNTDESC FROM ACCOUNTS ACC WHERE ACC.ACCOUNT = SUBSTR(P.ACCOUNT,1,5)) 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), A.ACCOUNTDESC ORDER BY P.COMPANY, SUBSTR(P.ACCOUNTID, 1,5)

But if you have an more effictive one I would love you to share it. If not still your help is greatly appreciated :) Thanks.
Maciej Los 18-Apr-13 4:41am

See my previous comment ;)
wonder-FOOL 18-Apr-13 4:44am

Just saw it. Thanks :) f I can 5+ you again I could but I have already did it :S

Top Experts
Last 24hrsThis month
 OriginalGriff 278 Sascha Lefèvre 105 RDBurmon 100 ppolymorphe 70 CPallini 60
 Dave Kreskowiak 1,866 Richard MacCutchan 1,260 OriginalGriff 1,114 d@nish 980 CPallini 810