Home » SQL & PL/SQL » SQL & PL/SQL » multiple grouping required vertically from single table (oracle 10G apex)
multiple grouping required vertically from single table [message #343052] Tue, 26 August 2008 01:57 Go to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
I have one table

 
Table name trntest

Tdate date,
Tamt  number(10,0)


I have data as follows

Tdate		tamt
01/01/2008	1000
01/02/2008      4000
01/03/2008	1000
01/04/2008	2000
01/05/2008	3000
01/06/2008	1000


I give criteria dates as 28/02/2008 and 31/05/2008 under ask variables

I want the following output

Tdate		tamt
28/02/2008	5000   (sum of tamt where tdate < 28/02/2008)
01/02/2008      4000
01/03/2008	1000
01/04/2008	2000
01/05/2008	3000
31/05/2008      15000   (sum of tamt where tdate < 31/05/2008)




Is it possible to achieve the result through select statement itself instead of writing procedures
yours
dr.s.raghunathan
Re: multiple grouping required vertically from single table [message #343064 is a reply to message #343052] Tue, 26 August 2008 03:08 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Dr. S -

I think I understand what you are looking for but am just confused with your example output -

Quote:

28/02/2008 5000 (sum of tamt where tdate < 28/02/2008)


The tamts tally 12,000 in January.
I also do not follow the order of desired output as you have
28/02/2008 then the january tdate entries, then the 31/05/2008 entry?

Leaving order aside, couldn't this all just be UNIONed together? :

(i'm not at an Oracle terminal,pardon the syntax, but here's the idea):
 
SELECT (
      Select to_date('02/28/2008','MM/DD/YYYY') from dual) 
       ) as tdate, 
       (
     select tsum from S1
       ) as tamt
FROM Dual

UNION ALL

Select tdate, tamt from trntest where tdate <  28/02/2008

UNION ALL

SELECT (
      Select to_date('05/31/2008','MM/DD/YYYY') from dual) 
       ) ,       (
     select tsum from S2
       )  
FROM Dual

UNION ALL

Select tdate,tamt from trntest where tdate between 28/02/2008 and 31/05/2008;



Hope this helps!

Regards,
Harry
Re: multiple grouping required vertically from single table [message #343069 is a reply to message #343064] Tue, 26 August 2008 03:27 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

sorry dates are in dd/mm/yyyy format. Now my requirement will be must more clearer.
yours
dr.s.raghunathan
Re: multiple grouping required vertically from single table [message #343070 is a reply to message #343052] Tue, 26 August 2008 03:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member


Well, for a start 28/02/2008 will result in a value of approximately 0.00697211....
Also
Quote:
...under ask variables ...

What are ask variables. I have never heard of them.
You have now been posting on orafaq for quite some time, surely you have figured out by now that you need to provide test cases along with expected results and most importantly, what you have already tried and the results of those attempts. Supplying nothing but an 'image' of the data, means that others have to spend their time building a test case on your behalf.
Why don't you (since you are the one in need of help) build that test case for those willing to help you (it is a simple courtesy).
Re: multiple grouping required vertically from single table [message #343072 is a reply to message #343070] Tue, 26 August 2008 03:56 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

whenever we give data through terminal and when it has been stored in memory variable we use to call ask variables for example
select * from some_table where coloumn_name = &dataplease;

here the dataplease is nothing but ask variable.

I have given my table structure and the data's fed and required output in my topic. I was not able to achieve it through single select statement.

I can able to achieve through creation of temprory table where i have pooled the data (which was required output) through creation of cursor and insert statement.

once again i reiterate the dates are in dd/mm/yyyy format. In the output first record says 28/02/2008 and Rs.5000 which is sum of 01/01/2008 Rs.1000 and 01/02/2008 Rs.4000

Even after this reply if my requirement is not understandable, then i have to concentrate much more on communication skills and will better it next time.

Any inconvenience caused under courtesy is the nature of my poor communication skill and not otherwise.

yours
dr.s.raghunathan


Re: multiple grouping required vertically from single table [message #343079 is a reply to message #343064] Tue, 26 August 2008 04:11 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

it seems that your solution will work for me. i will try at home and come back if needed. thanks for the efforts

yours
dr.s.raghunathan
Re: multiple grouping required vertically from single table [message #343130 is a reply to message #343079] Tue, 26 August 2008 06:38 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Glad to be of assistance; in my example I was not sure
how you wanted the order of the output, but that
would just change the UNION order (if you want the group record before or after the transaction records).

I drew upon the old day mainframe COBOL concept of control
break processing. I think my unkempt nested selects
can be "groomed" better. I too will try this out at
work today if I dont get swamped.

Regards,
Harry

Re: multiple grouping required vertically from single table [message #343143 is a reply to message #343130] Tue, 26 August 2008 07:44 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

I tried at home with the assistance i gained. The problem had been solved by following code

select to_date('&&start_date','DD-MM-YYYY') "Date" ,sum(tamt) from TRNTEST
where tdate < to_date('&start_date','DD-MM-YYYY')
union all
select tdate, tamt from TRNTEST
where tdate between to_date('&start_date','DD-MM-YYYY') and to_date('&&end_date','DD-MM-YYYY') 
union all
select to_date('&end_date','DD-MM-YYYY')  "Date" ,sum(tamt) from TRNTEST
where tdate < to_date('&end_date','DD-MM-YYYY') 

Date                      Amt                    
------------------------- ---------------------- 
01-FEB-08                 2800                   
01-FEB-08                 500                    
02-FEB-08                 300                    
03-FEB-08                 1400                   
28-FEB-08                 5000                   




for the given data sets are as follows
create table TRNTEST (tdate date, tamt number(10,2))

insert into TRNTEST values(to_date('01/01/2008','DD/MM/YYYY'),1000)
insert into TRNTEST values(to_date('02/01/2008','DD/MM/YYYY'),2000)
insert into TRNTEST values(to_date('03/01/2008','DD/MM/YYYY'),-200);
insert into TRNTEST values(to_date('01/02/2008','DD/MM/YYYY'),500);
insert into TRNTEST values(to_date('02/02/2008','DD/MM/YYYY'),300);
insert into TRNTEST values(to_date('03/02/2008','DD/MM/YYYY'),1400);
insert into TRNTEST values(to_date('01/03/2008','DD/MM/YYYY'),3000);
insert into TRNTEST values(to_date('02/03/2008','DD/MM/YYYY'),2000);



Conceptually you have guided and once again thank you very much
yours
dr.s.raghunathan
Previous Topic: oracle error (merged)
Next Topic: How to avoid duplicate rows from the result of a query ?
Goto Forum:
  


Current Time: Wed Dec 07 04:58:44 CST 2016

Total time taken to generate the page: 0.22592 seconds