OraFAQ Forum: SQL & PL/SQL » SQL to get amounts by type

Home » SQL & PL/SQL » SQL & PL/SQL » SQL to get amounts by type (Oracle RDBMS 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL to get amounts by type [message #642680]

Thu, 17 September 2015 04:19

oteixeira
Messages: 33
Registered: May 2007

Member

/forum/fa/12840/0/

Hi to all.
As an accountant, VAT has no much secrets for me. However, Tax Authorities sometimes request weird reports and I cannot say that I'm an sqlplus expert (far from it).
Please have a look to the uploaded file TwoTables.jpg
My goal is to use sqlplus to pick the data in the yellow table and return the data as shown on blue table.
Many thanks in advance for any kind help.
Octavio

Attachment: TwoTables.jpg
(Size: 41.54KB, Downloaded 1333 times)

Report message to a moderator

Re: SQL to get amounts by type [message #642681 is a reply to message #642680]

Thu, 17 September 2015 04:30

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use listagg, sum and group by

Report message to a moderator

Re: SQL to get amounts by type [message #642682 is a reply to message #642681]

Thu, 17 September 2015 04:49

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

cookiemonster wrote on Thu, 17 September 2015 15:00

Use listagg, sum and group by

But OP doesn't want aggregation of amount column here, he wants it to be pivoted.

Report message to a moderator

Re: SQL to get amounts by type [message #642683 is a reply to message #642682]

Thu, 17 September 2015 05:31

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Yup, wasn't paying attention.

@oteixeira - how many amount columns can you have - unlimited?

Report message to a moderator

Re: SQL to get amounts by type [message #642684 is a reply to message #642683]

Thu, 17 September 2015 05:39

oteixeira
Messages: 33
Registered: May 2007

Member

@cokiemonster - The source data is as shown on yellow table and i need the amounts in two separate columns (3 lines must become two).

Thank you all for helping.
Octavio

Report message to a moderator

Re: SQL to get amounts by type [message #642685 is a reply to message #642684]

Thu, 17 September 2015 05:54

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

How about if there are more lines, like 3 lines with TYPE 'B' and one 2 lines with type 'I'?

Edited: Typo

[Updated on: Thu, 17 September 2015 05:54]

Report message to a moderator

Re: SQL to get amounts by type [message #642686 is a reply to message #642684]

Thu, 17 September 2015 05:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

To help us to help you with any SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: SQL to get amounts by type [message #642688 is a reply to message #642685]

Thu, 17 September 2015 06:04

oteixeira
Messages: 33
Registered: May 2007

Member

@sandeep_orafaq - I see. I have to reformulate the question. Please consider that the third (last) line of yellow table is of type 'V'. Yes, 3 types instead of only 2.
Sorry for the mess...
Octavio

Report message to a moderator

Re: SQL to get amounts by type [message #642691 is a reply to message #642688]

Thu, 17 September 2015 06:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And what's the relationship between type B and type V?
What's the relationship between type and account?

Report message to a moderator

Re: SQL to get amounts by type [message #642694 is a reply to message #642688]

Thu, 17 September 2015 07:04

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

SO, now, Is your output still valid after you mentioned type 'V'?

[Updated on: Thu, 17 September 2015 07:04]

Report message to a moderator

Re: SQL to get amounts by type [message #642695 is a reply to message #642694]

Thu, 17 September 2015 07:12

oteixeira
Messages: 33
Registered: May 2007

Member

I will post a table with a test case.

Report message to a moderator

Re: SQL to get amounts by type [message #642704 is a reply to message #642695]

Thu, 17 September 2015 08:44

oteixeira
Messages: 33
Registered: May 2007

Member

I just uploaded two files:
expdat.dat (the table with source data)
result.jpg (showing the result i need)
Thanks for your patience.
Octavio

Attachment: expdat.dat
(Size: 16.00KB, Downloaded 1250 times)

Report message to a moderator

Re: SQL to get amounts by type [message #642706 is a reply to message #642704]

Thu, 17 September 2015 08:45

oteixeira
Messages: 33
Registered: May 2007

Member

It seems only one file can be uploaded at once, so here's the file showing the result as needed...

/forum/fa/12845/0/

/forum/fa/12845/0/

EDIT: embedded the image

Attachment: result.jpg
(Size: 36.40KB, Downloaded 1174 times)

[Updated on: Thu, 17 September 2015 08:54] by Moderator

Report message to a moderator

Re: SQL to get amounts by type [message #642708 is a reply to message #642706]

Thu, 17 September 2015 08:53

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I've not idea what type of file that .dat file is but it's not plain text.
You should just copy and paste a create table statement and insert statements directly in code tags.

Report message to a moderator

Re: SQL to get amounts by type [message #642713 is a reply to message #642708]

Thu, 17 September 2015 09:20

oteixeira
Messages: 33
Registered: May 2007

Member

Here are the create and insert statements:

CREATE TABLE TEST_CASE
(REC_NUMBER varchar2(20),
REC_AMOUNT number,
CASH_REC_ID number,
CCID number,
COMMENTS varchar2(20),
D_AMOUNT number)

INSERT INTO TEST_CASE(REC_NUMBER, REC_AMOUNT,CASH_REC_ID, CCID, COMMENTS, D_AMOUNT)
SELECT '363_2015', 26.87,366388,109740,'V',2.24 from dual
UNION ALL SELECT '363_2015', 26.87,366388,125191,'B',15.51 from dual
UNION ALL SELECT '363_2015', 26.87,366388,125047,'I',8.96 from dual
UNION ALL SELECT '670_15', 4.53,366640,125485,'I',4.53 from dual

Thank you
Octavio

Report message to a moderator

Re: SQL to get amounts by type [message #642715 is a reply to message #642713]

Thu, 17 September 2015 09:35

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And how do we know what individual records should be combined together?

Report message to a moderator

Re: SQL to get amounts by type [message #642716 is a reply to message #642715]

Thu, 17 September 2015 09:42

oteixeira
Messages: 33
Registered: May 2007

Member

For each REC_NUMBER, D_AMOUNT with COMMENTS = 'B' and 'V' must be shown on column AMOUNT1 and AMOUNT2, respectively. For each REC_NUMBER, D_AMOUNT with COMMENT = 'I' must be shown in AMOUNT1 and AMOUNT2 must show zero.
Thanks for your patiente.
Octavio

Report message to a moderator

Re: SQL to get amounts by type [message #642717 is a reply to message #642716]

Thu, 17 September 2015 10:15

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Can you have multiple records with the same rec_number and comments?

Report message to a moderator

Re: SQL to get amounts by type [message #642718 is a reply to message #642717]

Thu, 17 September 2015 10:17

oteixeira
Messages: 33
Registered: May 2007

Member

Yes, it is possible. In that case the SUM of D_AMOUNT can be used.

Report message to a moderator

Re: SQL to get amounts by type [message #642719 is a reply to message #642718]

Thu, 17 September 2015 10:19

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So can you post a test case which groups all the possible cases and with the result you want for it
along with the explanation for each line of this result so we can search and test some queries.

Report message to a moderator

Re: SQL to get amounts by type [message #642720 is a reply to message #642719]

Thu, 17 September 2015 10:23

oteixeira
Messages: 33
Registered: May 2007

Member

For the data i uploaded, the result must be the the one shown in result.jpg (on message #642706)
No other result is expected.

Report message to a moderator

Re: SQL to get amounts by type [message #642721 is a reply to message #642720]

Thu, 17 September 2015 10:27

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

But what you posted does not cover all the cases as the other posts showed it.
So a query which satisfies your first test case may be wrong for the real case
and so people who have worked on it had just waste their time.

Is this a so big effort for you to post a test case which will cover all the cases?
Is this a bigger effort than to find a solution to your problem?
If no then why should we take time to help you when you don't wnat to take time to help us to help you?

Report message to a moderator

Re: SQL to get amounts by type [message #642722 is a reply to message #642719]

Thu, 17 September 2015 10:28

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Looks like you need sum, case and union.
I think it's this:

SELECT rec_number, rec_amount, sum(CASE WHEN comments = 'B' THEN d_amount END) d_amount1,
sum(CASE WHEN comments = 'V' THEN d_amount END) d_amount2
FROM test_case
WHERE comments IN ('B', 'V')
GROUP BY rec_number, rec_amount
UNION ALL
SELECT rec_number, rec_amount, SUM(d_amount), 0
FROM test_case
WHERE comments = 'I'
GROUP BY rec_number, rec_amount

Report message to a moderator

Re: SQL to get amounts by type [message #642723 is a reply to message #642722]

Thu, 17 September 2015 10:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Though I suspect rec_amount should be calculated and not a column, which would make it more complicated

Report message to a moderator

Re: SQL to get amounts by type [message #642724 is a reply to message #642723]

Thu, 17 September 2015 10:35

oteixeira
Messages: 33
Registered: May 2007

Member

YES, that's it!
cookiemonster, thanks a million once again. BTW, is there a way of giving you some kind of points or prestige? I've been looking around but saw nothing...

Report message to a moderator

Re: SQL to get amounts by type [message #642727 is a reply to message #642724]

Thu, 17 September 2015 10:45

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

We don't have that concept here but thanks

Report message to a moderator

Previous Topic:	Pivot
Next Topic:	DBMS_OUTPUT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 14:56:22 CDT 2024