Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Oracle T-SQL
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
Thanks in advance.
Posted 17-Apr-13 22:00pm
Edited 17-Apr-13 22:02pm
Maciej Los151.7K
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

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)
  Permalink  
Comments
Aarti Meswania at 18-Apr-13 3:16am
   
5+! :)
Maciej Los at 18-Apr-13 3:20am
   
Thank you, Aarti ;)
Aarti Meswania at 18-Apr-13 3:24am
   
Welcome.
Maciej Los at 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 at 18-Apr-13 4:34am
   
lol
thanks. nice to meet u too
wonder-FOOL at 18-Apr-13 3:17am
   
Hello Maciej thank you very much for your fast response.
Maciej Los at 18-Apr-13 3:20am
   
You're welcome, call again ;)
wonder-FOOL at 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 at 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 at 18-Apr-13 4:39am
   
Discard my last question:
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 at 18-Apr-13 4:41am
   
See my previous comment ;)
wonder-FOOL at 18-Apr-13 4:44am
   
Just saw it. Thanks :) f I can 5+ you again I could but I have already did it :S

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

  Print Answers RSS
0 Maciej Los 295
1 OriginalGriff 273
2 Aajmot Sk 234
3 Marcin Kozub 205
4 Richard MacCutchan 200
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 18 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100