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 Go to next message
shahzad-ul-hasan
Messages: 561
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67241
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #628366 is a reply to message #628332] Mon, 24 November 2014 09:21 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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 #628377 is a reply to message #628375] Mon, 24 November 2014 10:16 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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 #628526 is a reply to message #628508] Wed, 26 November 2014 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 21575
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 67241
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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 #628680 is a reply to message #628665] Thu, 27 November 2014 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67241
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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.
/forum/fa/12335/0/


[mod-edit: image inserted into message body by bb]
  • Attachment: 445.JPG
    (Size: 96.22KB, Downloaded 540 times)

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 67241
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.

Re: Order by My grade (not Ascending.) [message #628882 is a reply to message #628881] Sun, 30 November 2014 01:40 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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 Go to previous messageGo to next message
John Watson
Messages: 8297
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 #628885 is a reply to message #628883] Sun, 30 November 2014 03:08 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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.
/forum/fa/12336/0/


[mod-edit: image inserted into message body by bb]
  • Attachment: 43333.JPG
    (Size: 26.69KB, Downloaded 487 times)

[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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: 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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
please see the my requirements./forum/fa/12338/0/. 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 471 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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;

/forum/fa/12340/0/


[mod-edit: imaged inserted into messaged body by bb]
  • Attachment: 334.JPG
    (Size: 28.14KB, Downloaded 451 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
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.
/forum/fa/12342/0/

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 426 times)

[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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: 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 Go to previous message
Littlefoot
Messages: 21575
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
shahzad-ul-hasan

thanks sir

Barbara is a lady.
Previous Topic: questions of matrix report
Next Topic: QR Code
Goto Forum:
  


Current Time: Sun Jul 12 21:08:38 CDT 2020