15,741,811 members
See more:
Hello all,

I need a slight change in my query to get me different result set. I have written the following query:
SQL
```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
Updated 17-Apr-13 21:02pm
v3

Solution 1

Try it:
SQL
```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