Home » Developer & Programmer » Reports & Discoverer » Rank/Position Calculation. (6i Reports)
Rank/Position Calculation. [message #625073] |
Wed, 01 October 2014 02:40 |
shahzad-ul-hasan
Messages: 625 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 #625084 is a reply to message #625082] |
Wed, 01 October 2014 04:20 |
shahzad-ul-hasan
Messages: 625 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...
-
Attachment: 2.JPG
(Size: 83.50KB, Downloaded 3185 times)
|
|
|
|
Re: Rank/Position Calculation. [message #625092 is a reply to message #625085] |
Wed, 01 October 2014 06:13 |
shahzad-ul-hasan
Messages: 625 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 #625101 is a reply to message #625095] |
Wed, 01 October 2014 06:38 |
shahzad-ul-hasan
Messages: 625 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 |
shahzad-ul-hasan
Messages: 625 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 |
cookiemonster
Messages: 13943 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 #625108 is a reply to message #625107] |
Wed, 01 October 2014 06:55 |
shahzad-ul-hasan
Messages: 625 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 #625114 is a reply to message #625110] |
Wed, 01 October 2014 08:11 |
shahzad-ul-hasan
Messages: 625 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 #625170 is a reply to message #625169] |
Thu, 02 October 2014 04:41 |
shahzad-ul-hasan
Messages: 625 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..
-
Attachment: 12.JPG
(Size: 83.81KB, Downloaded 3069 times)
|
|
|
|
|
|
Re: Rank/Position Calculation. [message #625178 is a reply to message #625173] |
Thu, 02 October 2014 05:15 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
shahzad-ul-hasan wrote on Thu, 02 October 2014 10:57Criteria 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 |
shahzad-ul-hasan
Messages: 625 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 #625187 is a reply to message #625184] |
Thu, 02 October 2014 08:29 |
shahzad-ul-hasan
Messages: 625 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 |
cookiemonster
Messages: 13943 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 #625193 is a reply to message #625191] |
Thu, 02 October 2014 08:57 |
cookiemonster
Messages: 13943 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 |
shahzad-ul-hasan
Messages: 625 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 |
cookiemonster
Messages: 13943 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 |
shahzad-ul-hasan
Messages: 625 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.
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 2830 times)
|
|
|
Goto Forum:
Current Time: Sat Sep 28 01:14:00 CDT 2024
|