Home » Developer & Programmer » Reports & Discoverer » Order by My grade (not Ascending.)
| Order by My grade (not Ascending.) [message #628292] |
Sun, 23 November 2014 06:15  |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
Dear i want to arrange the grade of the school from 'A+','A','B','C','D' etc.But when i run this query it will return 'A','A+','B','C','D'. Please advised how i can arrange these grades from 'A+','A','B','C','D'.
SELECT sno,gra, count(*)
FROM (select stuid,sno,
CASE WHEN perc >= 80 THEN 'A+'
WHEN perc >= 70 and perc <= 79 THEN 'A'
WHEN perc >= 60 and perc <= 69 THEN 'B'
WHEN perc >= 50 and perc <= 59 THEN 'C'
WHEN perc >= 40 and perc <= 49 THEN 'D'
WHEN perc >= 33 and perc <= 39 THEN 'E'
WHEN perc >= 0 AND PERC < 33 THEN 'F'
END AS gra
from (SELECT stuId,round(round(NVL(SUM(obtmarks),0))/round(NVL(SUM(totmarks),0))*100) AS perc,EXAMC,SEC,TEST.CLASS,sub.sno
FROM TEST,TEST1,STUDENT,CLASS,SUB
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls
and test.sec=:ssec
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and class.cname=test.class
and examc=:ty
group by TEST.class,examc,sec,stuid,sub.sno)
GROUP BY stuid,perc,sno
order by perc
)
group by sno,gra
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628294 is a reply to message #628292] |
Sun, 23 November 2014 06:32  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
order by sno, substr(gra,1,1), decode(substr(gra,2,1), '-',-1, '+',+1, 0) desc
[Updated on: Sun, 23 November 2014 06:33] Report message to a moderator |
|
|
|
| Re: Order by My grade (not Ascending.) [message #628332 is a reply to message #628294] |
Mon, 24 November 2014 03:04 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Or add another case and order by that:
CASE WHEN perc >= 80 THEN '1'
WHEN perc >= 70 and perc <= 79 THEN '2'
WHEN perc >= 60 and perc <= 69 THEN '3'
WHEN perc >= 50 and perc <= 59 THEN '4'
WHEN perc >= 40 and perc <= 49 THEN '5'
WHEN perc >= 33 and perc <= 39 THEN '6'
WHEN perc >= 0 AND PERC < 33 THEN '7'
END AS grade_order
|
|
|
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628375 is a reply to message #628371] |
Mon, 24 November 2014 09:58 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
SELECT sno,gra, count(*)
FROM (select stuid,sno,
CASE WHEN perc >= 80 THEN 'A+'
WHEN perc >= 70 and perc <= 79 THEN 'A'
WHEN perc >= 60 and perc <= 69 THEN 'B'
WHEN perc >= 50 and perc <= 59 THEN 'C'
WHEN perc >= 40 and perc <= 49 THEN 'D'
WHEN perc >= 33 and perc <= 39 THEN 'E'
WHEN perc >= 0 and perc < 33 THEN 'F'
END AS gra
or CASE WHEN perc >= 80 THEN '1'
WHEN perc >= 70 and perc <= 79 THEN '2'
WHEN perc >= 60 and perc <= 69 THEN '3'
WHEN perc >= 50 and perc <= 59 THEN '4'
WHEN perc >= 40 and perc <= 49 THEN '5'
WHEN perc >= 33 and perc <= 39 THEN '6'
WHEN perc >= 0 AND PERC < 33 THEN '7'
END AS grade_order
from (SELECT stuId,round(nvl(sum(obtmarks),0)/round(nvl(sum(totmarks),0)*100) AS perc,EXAMC,SEC,TEST.CLASS,sub.sno
FROM TEST,TEST1,STUDENT,CLASS,SUB
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls
and test.sec=:ssec
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and class.cname=test.class
and examc=:ty
group by TEST.class,examc,sec,stuid,sub.sno)
GROUP BY stuid,perc,sno
order by perc
)
group by sno,gra
please reconsider the above query.
|
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628508 is a reply to message #628377] |
Tue, 25 November 2014 20:24 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
1 SELECT sno,gra, count(*)
2 FROM (select stuid,sno,
3 CASE WHEN perc >= 80 THEN 'A+'
4 WHEN perc >= 70 and perc <= 79 THEN 'A'
5 WHEN perc >= 60 and perc <= 69 THEN 'B'
6 WHEN perc >= 50 and perc <= 59 THEN 'C'
7 WHEN perc >= 40 and perc <= 49 THEN 'D'
8 WHEN perc >= 33 and perc <= 39 THEN 'E'
9 WHEN perc >= 0 and perc < 33 THEN 'F'
10 END AS gra
11 or CASE WHEN perc >= 80 THEN '1'
12 WHEN perc >= 70 and perc <= 79 THEN '2'
13 WHEN perc >= 60 and perc <= 69 THEN '3'
14 WHEN perc >= 50 and perc <= 59 THEN '4'
15 WHEN perc >= 40 and perc <= 49 THEN '5'
16 WHEN perc >= 33 and perc <= 39 THEN '6'
17 WHEN perc >= 0 AND PERC < 33 THEN '7'
18 END AS grade_order
19 from (SELECT stuId,round(nvl(sum(obtmarks),0)/round(nvl(sum(totmarks),0)*100) AS perc,EXAMC,
20 FROM TEST,TEST1,STUDENT,CLASS,SUB
21 where test.tid=test1.tid
22 and test.tdate=test1.tdate
23 and test.class='EIGHT'
24 and test.sec='PINK'
25 and test1.tstuid=student.stuid
26 and student.STATUS='PRESENT'
27 and sub.subject=test.subject
28 and sub.cname=class.cname
29 and class.cname=test.class
30 and examc='1ST ASSESSMENT'
31 group by TEST.class,examc,sec,stuid,sub.sno)
32 GROUP BY stuid,perc,sno
33 order by perc
34 )
35* group by sno,gra,grade_order
QL> /
or CASE WHEN perc >= 80 THEN '1'
*
RROR at line 11:
RA-00923: FROM keyword not found where expected
|
|
|
|
|
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628665 is a reply to message #628650] |
Wed, 26 November 2014 20:47 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
see in that query nothing happen also.
SELECT sno,gra, count(*)
FROM (select stuid,sno,
Case When perc >= 80 THEN 'A+'
When perc >= 70 and perc <= 79 THEN 'A'
When perc >= 60 and perc <= 69 THEN 'B'
When perc >= 50 and perc <= 59 THEN 'C'
When perc >= 40 and perc <= 49 THEN 'D'
When perc >= 33 and perc <= 39 THEN 'E'
When perc >= 0 AND PERC < 33 THEN 'F'
end as gra
from (SELECT stuId,round(nvl(sum(obtmarks),0)/nvl(sum(totmarks),0)*100) AS perc,EXAMC,SEC,TEST.CLASS,sub.sno
FROM TEST,TEST1,STUDENT,SUB,class
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls
and test.sec=:ssec
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and examc=:ty
and class.cname=test.class
and class.cname=sub.cname
group by TEST.class,examc,sec,stuid,sub.sno)
GROUP BY stuid,perc,sno
order by sno, substr(gra,1,1), decode(substr(gra,2,1), '-',-1, '+',+1, 0) desc)
group by sno,gra |
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628704 is a reply to message #628644] |
Thu, 27 November 2014 03:51 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
shahzad-ul-hasan wrote on Wed, 26 November 2014 19:24cookiemonster suggested me to add or case in previous reply. So i add this case in that query.
Good grief. The word OR in my original post was to indicate you could use Michel's suggestion or mine. It does not indicate you need the OR keyword and you should know enough SQL by now to know that you can't put OR in the select part like that, it makes no sense.
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628880 is a reply to message #628704] |
Sat, 29 November 2014 23:15 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
SELECT gra,sno,count(*)
FROM (select
Case When perc >= 80 THEN 'A+'
When perc >= 70 and perc <= 79 THEN 'A'
When perc >= 60 and perc <= 69 THEN 'B'
When perc >= 50 and perc <= 59 THEN 'C'
When perc >= 40 and perc <= 49 THEN 'D'
When perc >= 33 and perc <= 39 THEN 'E'
When perc >= 0 AND PERC < 33 THEN 'F'
end as gra,stuid,sno
from (SELECT stuId,round(round(nvl(sum(obtmarks),0))/nvl(sum(totmarks),0)*100) AS perc,EXAMC,SEC,TEST.CLASS,sub.sno
FROM TEST,TEST1,STUDENT,SUB,class
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls
and test.sec=:ssec
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and examc=:ty
and class.cname=test.class
and class.cname=sub.cname
group by TEST.class,examc,sec,stuid,sub.sno)
GROUP BY stuid,perc,sno
order by substr(gra,1,1), decode(substr(gra,2,1), '-',-1, '+',+1, 0) desc,sno)
group by gra,sno
order by substr(gra,1,1), decode(substr(gra,2,1), '-',-1, '+',+1, 0) desc
This query counts some "A+" ,"A","B" and some "A","A+","B etc.please see the attached file. Also if no "A+",A,B then the C,D,E,F is show in front of A+,A. please advised. if A+ not present there then it will show 0 instead of the next grade.
[mod-edit: image inserted into message body by bb]
-
Attachment: 445.JPG
(Size: 96.22KB, Downloaded 2887 times)
[Updated on: Sun, 30 November 2014 03:04] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628882 is a reply to message #628881] |
Sun, 30 November 2014 01:40 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
Test case:
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');
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');
create table student (
stuid number(7) primary key,status varchar2(30),class varchar2(30),section varchar2(30),name varchar2(200),fname
varchar2(200));
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628883 is a reply to message #628882] |
Sun, 30 November 2014 02:20 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Shahzad, you must be precise when writing software. This statement,insert into test (tid,tdate,class,examc,subject,totmarks,sec)
values
(1,'23-SEP-2014','KG','1st Term Exams','English',50,A);
will not run because you have forgotten to enclose the last value in quotes. You are also relying on implicit type casting for the date value, which is a bug waiting to bite.
|
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628886 is a reply to message #628882] |
Sun, 30 November 2014 03:27 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have not provided any insert statements for the student table or an example of the results that you want based on the data provided, but I believe the query below is probably what you are looking for. It contains some corrections to your query and a slight modification of Michel's suggestion (removed one column from the order by clause that he provided).
SELECT sno, gra, count(*)
FROM (select stuid, sno,
CASE WHEN perc >= 80 THEN 'A+'
WHEN perc >= 70 and perc <= 79 THEN 'A'
WHEN perc >= 60 and perc <= 69 THEN 'B'
WHEN perc >= 50 and perc <= 59 THEN 'C'
WHEN perc >= 40 and perc <= 49 THEN 'D'
WHEN perc >= 33 and perc <= 39 THEN 'E'
WHEN perc >= 0 AND PERC < 33 THEN 'F'
END AS gra
from (SELECT stuId,
round(round(NVL(SUM(obtmarks),0))/round(NVL(SUM(totmarks),0))*100) AS perc,
EXAMC, SEC, TEST.CLASS, sub.sno
FROM TEST, TEST1, STUDENT, CLASS, SUB
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls
and test.sec=:ssec
and test1.tstuid=student.stuid
and student.STATUS='PRESENT'
and sub.subject=test.subject
and sub.cname=class.cname
and class.cname=test.class
and examc=:ty
group by TEST.class, examc, sec, stuid, sub.sno))
group by sno, gra
order by substr (gra,1,1), decode (substr (gra,2,1), '-', -1, '+', +1, 0) desc;
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628887 is a reply to message #628886] |
Sun, 30 November 2014 03:54 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
Insert into Student (stuid,,status,class,section,name,fname)
values
(22,'PRESENT','PREP','WHITE','ABC','DEF');
Insert into Student (stuid,,status,class,section,name,fname)
values
(23,'PRESENT','PREP','WHITE','AC','DF');
Insert into Student (stuid,,status,class,section,name,fname)
values
(24,'PRESENT','PREP','WHITE','A','D');
Insert into Student (stuid,,status,class,section,name,fname)
values
(25,'PRESENT','ONE','WHITE','C',F');
Insert into Student (stuid,,status,class,section,name,fname)
values
(26,'PRESENT','NURSERY','WHITE','B',F');
Insert into Student (stuid,,status,class,section,name,fname)
values
(27,'PRESENT','ONE','WHITE','E',D');
Same result of the above query.please view the attached file.
[mod-edit: image inserted into message body by bb]
-
Attachment: 43333.JPG
(Size: 26.69KB, Downloaded 2824 times)
[Updated on: Sun, 30 November 2014 04:05] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628892 is a reply to message #628891] |
Sun, 30 November 2014 06:09 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
please see the my requirements. . The red circle is my requirements. i want to count (A+,A,B,C,D,E,F) from each subject starting from A+, A,B,C and so on....
-
Attachment: 22.JPG
(Size: 70.13KB, Downloaded 2815 times)
[Updated on: Sun, 30 November 2014 06:11] Report message to a moderator |
|
|
|
| Re: Order by My grade (not Ascending.) [message #628920 is a reply to message #628892] |
Sun, 30 November 2014 18:08 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your insert statements have errors, such as double commas and missing quotes and implicit dates. Your insert statements don't match the data in your results and the data in the upper part of your results does not match the data in the lower part of the results. I tried to make some sense of it all and use some sample data that was similar to what is in the upper part of your results and here is what I came up with. The first query is just to show data. The second query is similar to what you were trying to do and the third query pivots the output in the manner that your desired results show.
SCOTT@orcl12c> COLUMN grade FORMAT A5
SCOTT@orcl12c> COLUMN range FORMAT A6
SCOTT@orcl12c> SELECT g.grade, g.low || '-' || g.high AS range, t.tid
2 FROM (SELECT 'A+' AS grade, 80 AS low, 100 AS high FROM DUAL UNION ALL
3 SELECT 'A' AS grade, 70 AS low, 79 AS high FROM DUAL UNION ALL
4 SELECT 'B' AS grade, 60 AS low, 69 AS high FROM DUAL UNION ALL
5 SELECT 'C' AS grade, 50 AS low, 59 AS high FROM DUAL UNION ALL
6 SELECT 'D' AS grade, 40 AS low, 49 AS high FROM DUAL UNION ALL
7 SELECT 'E' AS grade, 33 AS low, 39 AS high FROM DUAL UNION ALL
8 SELECT 'F' AS grade, 0 AS low, 32 AS high FROM DUAL) g,
9 (SELECT test1.tstuid, test1.tid,
10 ROUND ((test1.obtmarks/test.totmarks) * 100) perc
11 FROM test1, test
12 WHERE test1.tid = test.tid) t
13 WHERE g.low <= t.perc (+)
14 AND g.high >= t.perc (+)
15 ORDER BY SUBSTR (grade, 1, 1), DECODE (SUBSTR (grade, 2, 1), '-', 1, '+', -1, 0), tid
16 /
GRADE RANGE TID
----- ------ ----------
A+ 80-100 1
A+ 80-100 2
A+ 80-100 2
A+ 80-100 2
A+ 80-100 3
A+ 80-100 3
A+ 80-100 4
A+ 80-100 4
A+ 80-100 5
A+ 80-100 5
A+ 80-100 5
A+ 80-100 6
A+ 80-100 6
A 70-79 1
A 70-79 6
A 70-79 6
A 70-79 6
B 60-69 1
B 60-69 1
B 60-69 1
B 60-69 2
B 60-69 2
B 60-69 4
B 60-69 4
B 60-69 5
B 60-69 5
C 50-59 1
C 50-59 3
C 50-59 3
D 40-49 2
D 40-49 3
D 40-49 4
D 40-49 4
D 40-49 5
E 33-39
F 0-32 3
F 0-32 6
37 rows selected.
SCOTT@orcl12c> SELECT g.grade, g.low || '-' || g.high AS range, t.tid, COUNT(t.tid) AS cnt
2 FROM (SELECT 'A+' AS grade, 80 AS low, 100 AS high FROM DUAL UNION ALL
3 SELECT 'A' AS grade, 70 AS low, 79 AS high FROM DUAL UNION ALL
4 SELECT 'B' AS grade, 60 AS low, 69 AS high FROM DUAL UNION ALL
5 SELECT 'C' AS grade, 50 AS low, 59 AS high FROM DUAL UNION ALL
6 SELECT 'D' AS grade, 40 AS low, 49 AS high FROM DUAL UNION ALL
7 SELECT 'E' AS grade, 33 AS low, 39 AS high FROM DUAL UNION ALL
8 SELECT 'F' AS grade, 0 AS low, 32 AS high FROM DUAL) g,
9 (SELECT test1.tstuid, test1.tid,
10 ROUND ((test1.obtmarks/test.totmarks) * 100) perc
11 FROM test1, test
12 WHERE test1.tid = test.tid) t
13 WHERE g.low <= t.perc (+)
14 AND g.high >= t.perc (+)
15 GROUP BY g.grade, g.low, g.high, t.tid
16 ORDER BY SUBSTR (grade, 1, 1), DECODE (SUBSTR (grade, 2, 1), '-', 1, '+', -1, 0), tid
17 /
GRADE RANGE TID CNT
----- ------ ---------- ----------
A+ 80-100 1 1
A+ 80-100 2 3
A+ 80-100 3 2
A+ 80-100 4 2
A+ 80-100 5 3
A+ 80-100 6 2
A 70-79 1 1
A 70-79 6 3
B 60-69 1 3
B 60-69 2 2
B 60-69 4 2
B 60-69 5 2
C 50-59 1 1
C 50-59 3 2
D 40-49 2 1
D 40-49 3 1
D 40-49 4 2
D 40-49 5 1
E 33-39 0
F 0-32 3 1
F 0-32 6 1
21 rows selected.
SCOTT@orcl12c> SELECT grade, range,
2 SUM (DECODE (tid, 1, cnt, 0)) eng,
3 SUM (DECODE (tid, 3, cnt, 0)) urd,
4 SUM (DECODE (tid, 2, cnt, 0)) mat,
5 SUM (DECODE (tid, 4, cnt, 0)) isl,
6 SUM (DECODE (tid, 5, cnt, 0)) draw,
7 SUM (DECODE (tid, 6, cnt, 0)) hist
8 FROM (SELECT g.grade, g.low || '-' || g.high AS range, t.tid, COUNT(t.tid) AS cnt
9 FROM (SELECT 'A+' AS grade, 80 AS low, 100 AS high FROM DUAL UNION ALL
10 SELECT 'A' AS grade, 70 AS low, 79 AS high FROM DUAL UNION ALL
11 SELECT 'B' AS grade, 60 AS low, 69 AS high FROM DUAL UNION ALL
12 SELECT 'C' AS grade, 50 AS low, 59 AS high FROM DUAL UNION ALL
13 SELECT 'D' AS grade, 40 AS low, 49 AS high FROM DUAL UNION ALL
14 SELECT 'E' AS grade, 33 AS low, 39 AS high FROM DUAL UNION ALL
15 SELECT 'F' AS grade, 0 AS low, 32 AS high FROM DUAL) g,
16 (SELECT test1.tstuid, test1.tid,
17 ROUND ((test1.obtmarks/test.totmarks) * 100) perc
18 FROM test1, test
19 WHERE test1.tid = test.tid) t
20 WHERE g.low <= t.perc (+)
21 AND g.high >= t.perc (+)
22 GROUP BY g.grade, g.low, g.high, t.tid)
23 GROUP BY grade, range
24 ORDER BY SUBSTR (grade, 1, 1), DECODE (SUBSTR (grade, 2, 1), '-', 1, '+', -1, 0)
25 /
GRADE RANGE ENG URD MAT ISL DRAW HIST
----- ------ ---------- ---------- ---------- ---------- ---------- ----------
A+ 80-100 1 2 3 2 3 2
A 70-79 1 0 0 0 0 3
B 60-69 3 0 2 2 2 0
C 50-59 1 2 0 0 0 0
D 40-49 0 1 1 2 1 0
E 33-39 0 0 0 0 0 0
F 0-32 0 1 0 0 0 1
7 rows selected.
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628921 is a reply to message #628920] |
Sun, 30 November 2014 21:00 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
your query gives me more light to correct my equations.If some grade is not present then query will show 0 instead of null. please see the attached picture. please advised me in that query.
SELECT gra, sno,count(*)
FROM (select sno,
CASE WHEN perc >= 80 THEN 'A+'
WHEN perc >= 70 and perc <= 79 THEN 'A'
WHEN perc >= 60 and perc <= 69 THEN 'B'
WHEN perc >= 50 and perc <= 59 THEN 'C'
WHEN perc >= 40 and perc <= 49 THEN 'D'
WHEN perc >= 33 and perc <= 39 THEN 'E'
WHEN perc >= 0 AND PERC < 33 THEN 'F'
END AS gra
from (SELECT round(round(SUM(obtmarks))/round(SUM(totmarks))*100) AS perc, EXAMC, SEC, TEST.CLASS, sub.sno
FROM TEST, TEST1, STUDENT, CLASS, SUB
where test.tid=test1.tid
and test.tdate=test1.tdate
and test.class=:cls
and test.sec=:ssec
and test1.tstuid=student.stuid(+)
and student.STATUS='PRESENT'
and sub.subject=test.subject(+)
and sub.cname=class.cname
and class.cname=test.class
and examc=:ty
group by TEST.class, examc, sec, stuid, sub.sno))
group by sno, gra
order by decode (substr (gra,2,1), '-', -1, '+', +1, 0) desc;
[mod-edit: imaged inserted into messaged body by bb]
-
Attachment: 334.JPG
(Size: 28.14KB, Downloaded 2794 times)
[Updated on: Mon, 01 December 2014 00:46] by Moderator Report message to a moderator |
|
|
|
| Re: Order by My grade (not Ascending.) [message #628931 is a reply to message #628921] |
Mon, 01 December 2014 02:09 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The final query that I provided produces output like what you posted you wanted, so I don't know why you don't just use that. Your query only produces 3 columns. Are you using some pivoting feature in Discoverer to pivot that result set or what? If that is what you are doing and you are not getting the rows with zeroes in that process, then you need to add a virtual table of all possible combinations of grade and tid to your query and outer join to that. You also need to count(tid) not count(*). I have provided an example below.
SCOTT@orcl12c> COLUMN grade FORMAT A5
SCOTT@orcl12c> COLUMN range FORMAT A6
SCOTT@orcl12c> SELECT g.grade, g.tid, COUNT (t.tid) cnt -- use count(t.tid) not count(*)
2 FROM -- virtual table of all possible combinations of grades and tid
3 (SELECT grade, tid
4 FROM (SELECT 'A+' AS grade FROM DUAL UNION ALL
5 SELECT 'A' AS grade FROM DUAL UNION ALL
6 SELECT 'B' AS grade FROM DUAL UNION ALL
7 SELECT 'C' AS grade FROM DUAL UNION ALL
8 SELECT 'D' AS grade FROM DUAL UNION ALL
9 SELECT 'E' AS grade FROM DUAL UNION ALL
10 SELECT 'F' AS grade FROM DUAL),
11 (SELECT DISTINCT tid FROM test)) g,
12 -- data:
13 (SELECT tstuid, tid,
14 CASE WHEN perc >= 80 THEN 'A+'
15 WHEN perc >= 70 and perc <= 79 THEN 'A'
16 WHEN perc >= 60 and perc <= 69 THEN 'B'
17 WHEN perc >= 50 and perc <= 59 THEN 'C'
18 WHEN perc >= 40 and perc <= 49 THEN 'D'
19 WHEN perc >= 33 and perc <= 39 THEN 'E'
20 WHEN perc >= 0 AND PERC < 33 THEN 'F'
21 END AS grade
22 FROM (SELECT test1.tstuid, test1.tid,
23 ROUND ((test1.obtmarks/test.totmarks) * 100) AS perc
24 FROM test1, test
25 WHERE test1.tid = test.tid)) t
26 -- outer joins to obtain rows with no data:
27 WHERE g.grade = t.grade (+)
28 AND g.tid = t.tid (+)
29 -- grouping necessary for count:
30 GROUP BY g.grade, g.tid
31 -- ordering:
32 ORDER BY SUBSTR (grade, 1, 1), DECODE (SUBSTR (grade, 2, 1), '-', 1, '+', -1, 0), tid
33 /
GRADE TID CNT
----- ---------- ----------
A+ 1 1
A+ 2 3
A+ 3 2
A+ 4 2
A+ 5 3
A+ 6 2
A 1 1
A 2 0
A 3 0
A 4 0
A 5 0
A 6 3
B 1 3
B 2 2
B 3 0
B 4 2
B 5 2
B 6 0
C 1 1
C 2 0
C 3 2
C 4 0
C 5 0
C 6 0
D 1 0
D 2 1
D 3 1
D 4 2
D 5 1
D 6 0
E 1 0
E 2 0
E 3 0
E 4 0
E 5 0
E 6 0
F 1 0
F 2 0
F 3 1
F 4 0
F 5 0
F 6 1
42 rows selected.
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628932 is a reply to message #628931] |
Mon, 01 December 2014 02:27 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following incorporates more of your original query. However, it appears to contains unnecessary columns, tables, joins, and sums. However, that may be because your data is not totally representative of the problem. It also groups on sno instead of tid, so it changes the results drastically, since your data only contains three snos, unlike the six tid's, but that may be an error. I am providing it so that you an experiment with both and compare and see what you need.
SCOTT@orcl12c> COLUMN grade FORMAT A5
SCOTT@orcl12c> COLUMN range FORMAT A6
SCOTT@orcl12c> SELECT g.grade, g.sno, COUNT (t.sno) cnt -- use count(t.sno) not count(*)
2 FROM -- virtual table of all possible combinations of grades and sno
3 (SELECT grade, sno
4 FROM (SELECT 'A+' AS grade FROM DUAL UNION ALL
5 SELECT 'A' AS grade FROM DUAL UNION ALL
6 SELECT 'B' AS grade FROM DUAL UNION ALL
7 SELECT 'C' AS grade FROM DUAL UNION ALL
8 SELECT 'D' AS grade FROM DUAL UNION ALL
9 SELECT 'E' AS grade FROM DUAL UNION ALL
10 SELECT 'F' AS grade FROM DUAL),
11 (SELECT DISTINCT sno FROM sub)) g,
12 -- data:
13 (SELECT stuid, sno,
14 CASE WHEN perc >= 80 THEN 'A+'
15 WHEN perc >= 70 and perc <= 79 THEN 'A'
16 WHEN perc >= 60 and perc <= 69 THEN 'B'
17 WHEN perc >= 50 and perc <= 59 THEN 'C'
18 WHEN perc >= 40 and perc <= 49 THEN 'D'
19 WHEN perc >= 33 and perc <= 39 THEN 'E'
20 WHEN perc >= 0 AND PERC < 33 THEN 'F'
21 END AS grade
22 -- may contain unnecessary columns, tables, joins, and sums:
23 FROM (SELECT stuId,
24 round(round(NVL(SUM(obtmarks),0))/round(NVL(SUM(totmarks),0))*100) AS perc,
25 EXAMC,SEC,TEST.CLASS,sub.sno
26 FROM TEST,TEST1,STUDENT,CLASS,SUB
27 where test.tid=test1.tid
28 and test.tdate=test1.tdate
29 and test1.tstuid=student.stuid
30 and student.STATUS='PRESENT'
31 and sub.subject=test.subject
32 and sub.cname=class.cname
33 and class.cname=test.class
34 -- the next 3 lines are commented out due to unknown values for the variables:
35 -- and test.class=:cls
36 -- and test.sec=:ssec
37 -- and examc=:ty
38 group by TEST.class,examc,sec,stuid,sub.sno)) t
39 -- outer joins to obtain rows with no data:
40 WHERE g.grade = t.grade (+)
41 AND g.sno = t.sno (+)
42 -- grouping necessary for count:
43 GROUP BY g.grade, g.sno
44 -- ordering:
45 ORDER BY SUBSTR (grade, 1, 1), DECODE (SUBSTR (grade, 2, 1), '-', 1, '+', -1, 0), sno
46 /
GRADE SNO CNT
----- ---------- ----------
A+ 1 1
A+ 2 2
A+ 3 2
A 1 1
A 2 1
A 3 0
B 1 3
B 2 1
B 3 0
C 1 1
C 2 0
C 3 2
D 1 0
D 2 1
D 3 1
E 1 0
E 2 0
E 3 0
F 1 0
F 2 0
F 3 1
21 rows selected.
|
|
|
|
| Re: Order by My grade (not Ascending.) [message #628986 is a reply to message #628932] |
Mon, 01 December 2014 19:45 |
shahzad-ul-hasan
Messages: 640
Registered: August 2002
|
Senior Member |
|
|
thanks sir, with your amendments i got this result.please see the attached file.i want to match the sno with the header sno(subjects) so that the not present column automatically skip and place the next column in place that column.
i want to compare sno with above sno with subquery. but return more then one row.
Quote:
SELECT g.grade, g.sno, COUNT (t.sno) cnt -- use count(t.sno) not count(*)
2 FROM -- virtual table of all possible combinations of grades and sno
3 (SELECT grade, sno
4 FROM (SELECT 'A+' AS grade FROM DUAL UNION ALL
5 SELECT 'A' AS grade FROM DUAL UNION ALL
6 SELECT 'B' AS grade FROM DUAL UNION ALL
7 SELECT 'C' AS grade FROM DUAL UNION ALL
8 SELECT 'D' AS grade FROM DUAL UNION ALL
9 SELECT 'E' AS grade FROM DUAL UNION ALL
10 SELECT 'F' AS grade FROM DUAL),
11 (SELECT DISTINCT sno FROM sub)) g,
At line 11 i have used this code.
11 (SELECT DISTINCT sno FROM sub where sub.subject=(select subject from test where class='PREP' and test.sec='WHI' AND test.examc='1ST')) g,
-
Attachment: 45333.JPG
(Size: 96.24KB, Downloaded 2771 times)
[Updated on: Mon, 01 December 2014 19:48] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Dec 27 12:32:36 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
