SUM on a column query

Question

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 17-Apr-13 21:00pm

wonder-FOOL

Updated 17-Apr-13 21:02pm

Maciej Los

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-04-17T21:05:00

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)

Posted 17-Apr-13 21:05pm

Maciej Los

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

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

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 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