Home » Developer & Programmer » Reports & Discoverer » Order by My grade (not Ascending.)
Order by My grade (not Ascending.) Sun, 23 November 2014 06:15
 shahzad-ul-hasan Messages: 600Registered: 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: 68259Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: 13894Registered: 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'
```
Re: Order by My grade (not Ascending.) [message #628366 is a reply to message #628332] Mon, 24 November 2014 09:21
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
where i can place this case after the first case.
Re: Order by My grade (not Ascending.) [message #628371 is a reply to message #628366] Mon, 24 November 2014 09:38
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
yes, or before - it needs to be at the same level (not in the select below or the one above) apart from that it doesn't matter.
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: 600Registered: 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'

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 #628377 is a reply to message #628375] Mon, 24 November 2014 10:16
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
Reconsider what exactly?
You'll need to add grade_order to the group by.
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: 600Registered: 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 #628526 is a reply to message #628508] Wed, 26 November 2014 00:59
 Littlefoot Messages: 21736Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
That doesn't make sense. What is OR doing here?
Re: Order by My grade (not Ascending.) [message #628644 is a reply to message #628526] Wed, 26 November 2014 13:24
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
cookiemonster suggested me to add or case in previous reply. So i add this case in that query.
Re: Order by My grade (not Ascending.) [message #628650 is a reply to message #628644] Wed, 26 November 2014 14:14
 Michel Cadot Messages: 68259Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

If you are unable to apply cookiemonster's, use mine, it does not require to modify your first query just to the ORDER BY clause.

[Updated on: Wed, 26 November 2014 14:14]

Report message to a moderator

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: 600Registered: 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 #628680 is a reply to message #628665] Thu, 27 November 2014 00:30
 Michel Cadot Messages: 68259Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

The ORDER BY should be AT THE END of the query.

Re: Order by My grade (not Ascending.) [message #628704 is a reply to message #628644] Thu, 27 November 2014 03:51
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
shahzad-ul-hasan wrote on Wed, 26 November 2014 19:24
cookiemonster 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: 600Registered: 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

[Updated on: Sun, 30 November 2014 03:04] by Moderator

Report message to a moderator

Re: Order by My grade (not Ascending.) [message #628881 is a reply to message #628880] Sun, 30 November 2014 01:14
 Michel Cadot Messages: 68259Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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.

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: 600Registered: 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: 8738Registered: 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 #628885 is a reply to message #628883] Sun, 30 November 2014 03:08
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
noted well. thanks for correcting me.
Re: Order by My grade (not Ascending.) [message #628886 is a reply to message #628882] Sun, 30 November 2014 03:27
 Barbara Boehmer Messages: 8922Registered: 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: 600Registered: 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

[Updated on: Sun, 30 November 2014 04:05] by Moderator

Report message to a moderator

Re: Order by My grade (not Ascending.) [message #628891 is a reply to message #628887] Sun, 30 November 2014 04:39
 Barbara Boehmer Messages: 8922Registered: November 2002 Location: California, USA Senior Member
Your requirements are unclear. You need to post what results you want, based on the data provided.
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: 600Registered: 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

[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: 8922Registered: 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  /

----- ------ ----------
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: 600Registered: 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

[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: 8922Registered: 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:
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  /

----- ---------- ----------
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: 8922Registered: 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:
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  /

----- ---------- ----------
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: 600Registered: 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

[Updated on: Mon, 01 December 2014 19:48]

Report message to a moderator

Re: Order by My grade (not Ascending.) [message #628989 is a reply to message #628986] Mon, 01 December 2014 21:44
 Barbara Boehmer Messages: 8922Registered: November 2002 Location: California, USA Senior Member
Use IN instead of =.

(SELECT DISTINCT sno FROM sub where sub.subject IN (select subject from test where class='PREP' and test.sec='WHI' AND test.examc='1ST'))) g,
Re: Order by My grade (not Ascending.) [message #628995 is a reply to message #628986] Tue, 02 December 2014 00:21
 Littlefoot Messages: 21736Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator