Home » Developer & Programmer » Reports & Discoverer » Rank/Position Calculation. (6i Reports)
Rank/Position Calculation. [message #625073] Wed, 01 October 2014 02:40 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Dear
Please look at the data and advised me how i can calculate the 1st, 2nd, 3rd position of the student.
Class:    Five
Sno	Name	Tot	Obt marks	%
1	A	20	15.2	     76.00
2	B	20	9.5	     47.50
3	C	20	13.25	     66.25
4	D	20	7	     35.00
5	E	20	4	     20.00
6	F	20	17	     85.00
7	G	20	9	     45.00
8	H	20	5	     25.00

Re: Rank/Position Calculation. [message #625079 is a reply to message #625073] Wed, 01 October 2014 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
look at the rank analytic function.
Re: Rank/Position Calculation. [message #625081 is a reply to message #625079] Wed, 01 October 2014 04:08 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
how i can use rank function in formula column . the percentage column is calculated item.
Re: Rank/Position Calculation. [message #625082 is a reply to message #625081] Wed, 01 October 2014 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can't you calculate it in the query itself?
Re: Rank/Position Calculation. [message #625083 is a reply to message #625081] Wed, 01 October 2014 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data and show you how to do it.

Re: Rank/Position Calculation. [message #625084 is a reply to message #625082] Wed, 01 October 2014 04:20 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SELECT  distinct sub.sno,TEST1.OBTMARKS, TEST1.PERCT,  TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS|| '/' ||TEST.SEC sec, SUB.SUBJECT, STUDENT.STUID, STUDENT.NAME
FROM sub,TEST1, TEST, STUDENT,class
WHERE LTRIM(TEST.SUBJECT)=RTRIM(SUB.SUBJECT)
AND TEST.CLASS=CLASS.cname
and class.cname=sub.cname
and TEST1.TDATE = TEST.TDATE
 AND TEST1.TID = TEST.TID
 AND STUDENT.STUID = TEST1.TSTUID
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno

function CF_1Formula return Number is
begin
  RETURN(Round((NVL(:SOBT,0)/NVL(:CS_TOT,0))*100));
end;

Please see the attached file...
/forum/fa/12197/0/
  • Attachment: 2.JPG
    (Size: 83.50KB, Downloaded 858 times)
Re: Rank/Position Calculation. [message #625085 is a reply to message #625084] Wed, 01 October 2014 04:51 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not a test case and it's not clear what sobt and cs_tot are.
Re: Rank/Position Calculation. [message #625092 is a reply to message #625085] Wed, 01 October 2014 06:13 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Create table Test (
 TID   NUMBER(9),TDATE DATE, CLASS  VARCHAR2(30), EXAMC VARCHAR2(80),SUBJECT  VARCHAR2(70),
 TOTMARKS NuMBER(3),SEC VARCHAR2(50),
 constraints test_pk primary key (tid,tdate));
Create table test1 (
TID   NUMBER(9),TDATE DATE,TSTUID  NUMBER(7),OBTMARKS     NUMBER(5,2),PERCT  NUMBER(6,2), REMARK                                             VARCHAR2(300),
constraints test_fk fireign key (tid,tdate) references test(tid,tdate));

Re: Rank/Position Calculation. [message #625095 is a reply to message #625092] Wed, 01 October 2014 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is awful, post it again with a correct format and add the INSERT statements for the data and the result you want for them.

If you make any effort to post your questions why should we make any effort to help you?

Re: Rank/Position Calculation. [message #625097 is a reply to message #625095] Wed, 01 October 2014 06:25 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
my google chrome crashes that why i could not load the all information. i will send it again.
Re: Rank/Position Calculation. [message #625100 is a reply to message #625097] Wed, 01 October 2014 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, we're waiting for you...

Re: Rank/Position Calculation. [message #625101 is a reply to message #625095] Wed, 01 October 2014 06:38 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Create table test (
  tid number(9),tdate date, class varchar2(30),examc varchar2(80),subject varchar2(70),
  totmarks number(3),sec varchar2(50)
  constraints test_pk primary key (tid,tdate));
 
  insert into test (tid,tdate,class,examc,subject,totmarks,sec)
  values
 (1,'23-SEP-2014','KG','1st Term Exams','English',50,A);

 insert into test (tid,tdate,class,examc,subject,totmarks,sec)
 values
 (2,'23-SEP-2014','KG','1st Term Exams','Math',50,A);

 insert into test (tid,tdate,class,examc,subject,totmarks,sec)
 values
 (3,'23-SEP-2014','KG','1st Term Exams','Urdu',40,A);


Create table test1 (
  tid number(9),tdate date,tstuid number(7),obtmarks number(5,2),perct number(6,2),
  remark varchar2(300),constraints test_fk foreign key (tid,tdate) references  
  test(tid,tdate));

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (1,'23-SEP-2014',22,40,80.00,'Good');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (1,'23-SEP-2014',23,33,66.00,'Good');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (2,'23-SEP-2014',24,25.5,51.00,'Average');
 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (2,'23-SEP-2014',22,33,70.00,'Good');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (2,'23-SEP-2014',23,33,66.00,'Good');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (2,'23-SEP-2014',24,25,51.00,'Average');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (3,'23-SEP-2014',22,40,80.00,'Good');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (3,'23-SEP-2014',23,33,66.00,'Good');

 insert into test1 (tid,tdate,tstuid,obtmarks,perct,remark)
 values
 (3,'23-SEP-2014',24,25.5,51.00,'Average');

Re: Rank/Position Calculation. [message #625104 is a reply to message #625101] Wed, 01 October 2014 06:47 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Create Table Class (
Cname varchar2(70) primary key);

  Insert into class (cname) values ('KG');
  Insert into class (cname) values ('I');
  Insert into class (cname) values ('II');
 
Create table sub (
 cname varchar2(70) references class(cname),sno number(3),subject varchar2(70));

 Insert into sub (cname,sno,subject)
 values 
 ('KG',1,'English');
 Insert into sub (cname,sno,subject)
 values 
 ('KG',2,'Math');
 Insert into sub (cname,sno,subject)
 values 
 ('KG',3,'Urdu');
 Insert into sub (cname,sno,subject)
 values 
 ('I',1,'English');
 Insert into sub (cname,sno,subject)
 values 
 ('I',2,'Math');
 Insert into sub (cname,sno,subject)
 values 
 ('I',3,'Urdu');

Re: Rank/Position Calculation. [message #625105 is a reply to message #625101] Wed, 01 October 2014 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should always test such scripts before posting them:
SQL> Create table test (
  tid number(9),tdate date, class varchar2(30),examc varchar2(80),subject varchar2(70),
  totmarks number(3),sec varchar2(50)
  constraints test_pk primary key (tid,tdate));  2    3    4
  constraints test_pk primary key (tid,tdate))
                                  *
ERROR at line 4:
ORA-00907: missing right parenthesis


SQL>

Re: Rank/Position Calculation. [message #625107 is a reply to message #625105] Wed, 01 October 2014 06:52 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Create table test (
  tid number(9),tdate date, class varchar2(30),examc varchar2(80),subject varchar2(70),
  totmarks number(3),sec varchar2(50),
  constraints test_pk primary key (tid,tdate));
 
Re: Rank/Position Calculation. [message #625108 is a reply to message #625107] Wed, 01 October 2014 06:55 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
 insert into test (tid,tdate,class,examc,subject,totmarks,sec)
  values
 (1,'23-SEP-2014','KG','1st Term Exams','English',50,'A');

 insert into test (tid,tdate,class,examc,subject,totmarks,sec)
 values
 (2,'23-SEP-2014','KG','1st Term Exams','Math',50,'A');

 insert into test (tid,tdate,class,examc,subject,totmarks,sec)
 values
 (3,'23-SEP-2014','KG','1st Term Exams','Urdu',40,'A');

Re: Rank/Position Calculation. [message #625110 is a reply to message #625108] Wed, 01 October 2014 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, you have test, test1, class and what is the result you want, explaining where all the result columns come from.

Re: Rank/Position Calculation. [message #625114 is a reply to message #625110] Wed, 01 October 2014 08:11 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
 create table student (
 stuid number(7) primary key,status varchar2(30),class varchar2(30),section varchar2(30),name varchar2(200),fname    
 varchar2(200));


That is main query for report.

 select  distinct sub.sno,test1.obtmarks, test1.perct,test.examc,test.totmarks, test.class|| '/' ||test.sec,  
 sub.subject, student.stuid,student.name 
 from sub,test1, test, student,class
 where LTRIM(test.subject)=RTRIM(sub.subject)
 and test.class=class.cname
 and class.cname=sub.cname
 and test1.tdate = test.tdate
 and test1.tid = test.tid
 and student.studid = test.tstuid
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno


result required for 2.jpg. (1st position , 2nd position, 3rd position of the students) in my previous reply.
Re: Rank/Position Calculation. [message #625159 is a reply to message #625114] Thu, 02 October 2014 03:10 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
/forum/fa/12202/0/
  • Attachment: 3.JPG
    (Size: 22.74KB, Downloaded 819 times)
Re: Rank/Position Calculation. [message #625160 is a reply to message #625159] Thu, 02 October 2014 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
what's your database version?
Re: Rank/Position Calculation. [message #625161 is a reply to message #625160] Thu, 02 October 2014 03:23 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Oracle 9i (ver . 2) reports 6i
Re: Rank/Position Calculation. [message #625164 is a reply to message #625161] Thu, 02 October 2014 04:16 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
reports 6i doesn't recognize rank, oracle DB 9i does.
So create a view that does all the calculations you need then query that from the report.
Re: Rank/Position Calculation. [message #625165 is a reply to message #625164] Thu, 02 October 2014 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
And you can't use rank in a query against dual like that it makes no sense, you have to apply it to the base tables.
Re: Rank/Position Calculation. [message #625167 is a reply to message #625164] Thu, 02 October 2014 04:23 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
How i can calculate these calculation.i have created the view.sum up the total marka and sum obtained marks. and how i can used rank function in query;
Re: Rank/Position Calculation. [message #625169 is a reply to message #625167] Thu, 02 October 2014 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What is the view query?
Have you read the documentation on rank?
Re: Rank/Position Calculation. [message #625170 is a reply to message #625169] Thu, 02 October 2014 04:41 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SELECT  distinct sub.sno,TEST1.OBTMARKS, TEST1.PERCT,  TEST.EXAMC, TEST.TOTMARKS, TEST.CLASS|| '/' ||TEST.SEC sec, SUB.SUBJECT, STUDENT.STUID, ' '||STUDENT.NAME name,
rank() over (partition by nvl(obtmarks,0)/nvl(totmarks,0)*100 order by stuid) aaa
FROM sub,TEST1, TEST, STUDENT,class
WHERE LTRIM(TEST.SUBJECT)=RTRIM(SUB.SUBJECT)
AND TEST.CLASS=CLASS.cname
and class.cname=sub.cname
and TEST1.TDATE = TEST.TDATE
 AND TEST1.TID = TEST.TID
 AND STUDENT.STUID = TEST1.TSTUID
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno

And the Output is (Wrong): (How it can correct..
/forum/fa/12203/0/
  • Attachment: 12.JPG
    (Size: 83.81KB, Downloaded 735 times)
Re: Rank/Position Calculation. [message #625172 is a reply to message #625170] Thu, 02 October 2014 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What is the criteria for determining rank?
I'm pretty sure you shouldn't be partitioning by %age.
Re: Rank/Position Calculation. [message #625173 is a reply to message #625172] Thu, 02 October 2014 04:57 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Criteria for determining rank? partitioning by %age.
Re: Rank/Position Calculation. [message #625174 is a reply to message #625173] Thu, 02 October 2014 05:03 Go to previous messageGo to next message
Littlefoot
Messages: 21575
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you want to sort them by newly calculated rank value, why did you put
order by sub.sno
into the query? Try with
select rank_value,
       <all other columns you need>
from (your query goes here)
order by rank_value
instead.
Re: Rank/Position Calculation. [message #625178 is a reply to message #625173] Thu, 02 October 2014 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
shahzad-ul-hasan wrote on Thu, 02 October 2014 10:57
Criteria for determining rank? partitioning by %age.


If you think that you don't know what partitioning does.
Don't explain with reference to how you think the rank function works, explain the rules in simple English.
Re: Rank/Position Calculation. [message #625183 is a reply to message #625178] Thu, 02 October 2014 08:04 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Quote:
Criteria for determining rank? partitioning by %age.

My means is that i want to partition the query/table on percentage which i calculated by .

function CF_1Formula return Number is
begin
  RETURN(Round((NVL(:SOBT,0)/NVL(:CS_TOT,0))*100));
end;


:SOBT means student obtained marks and :cs_tot means total no of the 6 or 8 subjects.
Re: Rank/Position Calculation. [message #625184 is a reply to message #625183] Thu, 02 October 2014 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So If you have 10 students each with a different % score they'll all have a rank of 1. Is that really what you want?
Cause that's what partitioning by % gives.
Re: Rank/Position Calculation. [message #625185 is a reply to message #625174] Thu, 02 October 2014 08:20 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
i cannot understand how i can use your query
select rank_value,
       <all other columns you need>
from (your query goes here)
order by rank_value
Re: Rank/Position Calculation. [message #625187 is a reply to message #625184] Thu, 02 October 2014 08:29 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
yes if the students have diffrent like that:
S-No  Name      Eng+Urdu+math  Total(Subj)    ObtTot    %      Rank
1     A         30+40+52       200            122       61      3     
2     B         40+41+70       200            151       75      2        
3     C         41+42+80       200            163       82      1
4     D         41+42+80.2     200            163.2     82      1
5     E         30+30+30       200            90        45      4

That's the result i want.
Re: Rank/Position Calculation. [message #625190 is a reply to message #625187] Thu, 02 October 2014 08:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You really don't like explaining rules in words do you? Example output is always helpful but it doesn't always make clear what required logic is.
You want students ranked by percentage.
The question is do subsets of students belong to different ranked sets, or should all the students in the output be in the same ranked list?
e.g.
SQL> WITH DATA AS (SELECT CASE WHEN ROWNUM < 5 THEN 'A' ELSE 'B' END AS class,
  2                ROWNUM stuid,
  3                ROWNUM * 10 percentage FROM dual CONNECT BY LEVEL < 10)
  4  SELECT class,
  5         stuid,
  6         percentage,
  7         RANK () OVER ( order by percentage DESC) total_rank,
  8         RANK () OVER (PARTITION BY CLASS order by percentage DESC) rank_per_class
  9  FROM DATA;
 
CLASS      STUID PERCENTAGE TOTAL_RANK RANK_PER_CLASS
----- ---------- ---------- ---------- --------------
B              9         90          1              1
B              8         80          2              2
B              7         70          3              3
B              6         60          4              4
B              5         50          5              5
A              4         40          6              1
A              3         30          7              2
A              2         20          8              3
A              1         10          9              4
 
9 rows selected
 
SQL> 

The rank needs to order by percentage, what isn't clear is whether you need a partition by clause and if so what it should be.
Re: Rank/Position Calculation. [message #625191 is a reply to message #625184] Thu, 02 October 2014 08:44 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Your example is good but how i can used this query into main report query???

[Updated on: Thu, 02 October 2014 08:44]

Report message to a moderator

Re: Rank/Position Calculation. [message #625193 is a reply to message #625191] Thu, 02 October 2014 08:57 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't really know since you still haven't really explained what you want generally, or even what the correct rank calculation is.
I know they say a picture paints a 1000 words but that's really not true here.
Rather it's a case of: a few simple sentences explaining your requirements would paint a 1000 pictures.

Something along lines of:
For each student I want to show a,b,c and d
d is calculated as e,f and g
Then I want to calculate a rank for each student in a given group (or not)

Above explanation should make no reference to summary or formula columns that we have no access to - it should refer to the appropriate columns from the tables themselves
Re: Rank/Position Calculation. [message #625196 is a reply to message #625193] Thu, 02 October 2014 10:02 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Quote:
For each student I want to show a,b,c and d
d is calculated as e,f and g
Then I want to calculate a rank for each student in a given group (or not)

" For each student i want to show d calculate a rank for each student in a given group " that is i want
Re: Rank/Position Calculation. [message #625199 is a reply to message #625196] Thu, 02 October 2014 10:34 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
ok - either you start supplying proper information - it'll take more than one sentence - or I stop helping you.
I asked for a detailed explanation of all the data you want the report to display.
I have no idea what your definition of a group of students is.
Re: Rank/Position Calculation. [message #625203 is a reply to message #625199] Thu, 02 October 2014 10:52 Go to previous messageGo to previous message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
I want to made a matrix with group report class wise to display the student (id,name,subject numbers, obtained marks in subjects, total obtained marks, percentage, and position in the class or(rank in class).
i am using this main report query. table structure i already provided to you.
select  distinct sub.sno,test1.obtmarks, test1.perct,test.examc,test.totmarks, test.class|| '/' ||test.sec,  
 sub.subject, student.stuid,student.name 
 from sub,test1, test, student,class
 where LTRIM(test.subject)=RTRIM(sub.subject)
 and test.class=class.cname
 and class.cname=sub.cname
 and test1.tdate = test.tdate
 and test1.tid = test.tid
 and student.studid = test.tstuid
and student.status='PRESENT'
and test.examc=:ty
and student.class=:cls
and section=:ssec
order by sub.sno

the report format is in correct shape except only the (rank/position in the class not calculated correctly).if the percentage of the student is greater than the other students its rank/position in the class would be 1.than 2, 3......and so on. till last record of that class.if some student has equal percentage than rank/position will be decided by percentage decimal./forum/fa/12207/0/
in this picture the detail of all subjects, students, obtained marks, subjects marks shown. and i highlight the formula column. The POS is empty. in that column the result of rank should be displayed based on %age column.
  • Attachment: 2.JPG
    (Size: 83.50KB, Downloaded 638 times)
Previous Topic: how to get the values asc order??
Next Topic: drill down reports in oracle reports10g
Goto Forum:
  


Current Time: Fri Jul 10 01:05:05 CDT 2020