Click here to Skip to main content
15,907,687 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

Thanks in advance.
Posted
Updated 17-Apr-13 21:02pm
v3

1 solution

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)
 
Share this answer
 
Comments
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900