Home » SQL & PL/SQL » SQL & PL/SQL » need help on query
need help on query [message #189393] Thu, 24 August 2006 07:22 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
DROP TABLE T;
create table t (acctnum varchar2(9),
year date,
dividend number);

insert into t values ('03E005883','01-JAN-06',100);
insert into t values ('03E005883','01-JAN-07',200);
insert into t values ('517000000','01-MAR-09',500);
insert into t values ('517000000','01-MAR-10',100);
COMMIT;

SELECT * FROM T;

ACCTNUM YEAR DIVIDEND
--------- --------- ----------
03E005883 01-JAN-06 100
03E005883 01-JAN-07 200
517000000 01-MAR-09 500
517000000 01-MAR-10 100

I want to fetch the data as follows:

ACCTNUM YEAR,SUM_OF_DIVIDEND,PERCENT OF DIVIDEND for that year.


select to_char(year,'YYYY') year,sum(dividend) divi,
sum(dividend)/(select sum(dividend) from t where acctnum = '03E005883') PERCENT_DIVI
from t where acctnum = '03E005883'
group by to_char(year,'YYYY');

YEAR DIVI PERCENT_DIVI
---- ---------- ------------
2006 100 .333333333
2007 200 .666666667

Can we acheive this in analytical functions? Which is the better way to acheive the same?
Thanks.
Re: need help on query [message #189395 is a reply to message #189393] Thu, 24 August 2006 07:55 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent, saved me time by having the creates and inserts. Here is one way.
tstdb SCOTT> l
  1  select to_char(year,'yyyy'), sum(dividend),
  2     ratio_to_report(sum(dividend)) over () as pcttot
  3  from t
  4  where acctnum = '03E005883'
  5* group by to_char(year,'yyyy')
tstdb SCOTT> /

TO_C SUM(DIVIDEND)     PCTTOT
---- ------------- ----------
2006           100 .333333333
2007           200 .666666667

[Updated on: Thu, 24 August 2006 07:58]

Report message to a moderator

Re: need help on query [message #189400 is a reply to message #189395] Thu, 24 August 2006 08:24 Go to previous message
gkodakalla
Messages: 49
Registered: March 2005
Member
Thanks a lot for your quick reply.

Giridhar
Previous Topic: know NOTHING about Oracle, but have a dmp file
Next Topic: candidate key
Goto Forum:
  


Current Time: Fri Dec 09 21:14:39 CST 2016

Total time taken to generate the page: 0.08231 seconds