Home » Developer & Programmer » Reports & Discoverer » Count (A+,A,B) from grade column (6i,Oracle,9i Rel.2)
Count (A+,A,B) from grade column [message #625737] Mon, 13 October 2014 13:33 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Dear please see the attached file. i want to count the no of grades (How many A+, How many, A, how Many B etc).... from grade column.
/forum/fa/12225/0/
  • Attachment: 1322.JPG
    (Size: 26.48KB, Downloaded 641 times)
Re: Count (A+,A,B) from grade column [message #625739 is a reply to message #625737] Mon, 13 October 2014 13:37 Go to previous messageGo to next message
Littlefoot
Messages: 21572
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to the date you registered on OraFAQ Forum, you are using Oracle for 12 years now. I'd expect you to be able to solve such a problem by yourself. So, which query did you manage to write so far?
Re: Count (A+,A,B) from grade column [message #625755 is a reply to message #625739] Mon, 13 October 2014 22:35 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member

  1  Select  decode( 'E', '33-39',
  2                  'D', '40-49',
  3             'C', '50-59',
  4             'B', '60-69',
  5             'A', '70-79',
  6             'A+','80% and above'
  7          ),count(tstuid) from test1
  8  group by  decode( 'E', '33-39',
  9                  'D', '40-49',
 10             'C', '50-59',
 11             'B', '60-69',
 12             'A', '70-79',
 13             'A+','80% and above'
 14*         )
SQL> /

DECODE('E','3 COUNT(TSTUID)
------------- -------------
80% and above         28000

Re: Count (A+,A,B) from grade column [message #625787 is a reply to message #625755] Tue, 14 October 2014 04:11 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Take a long hard look at that decode, it's missing an important parameter.
Re: Count (A+,A,B) from grade column [message #625804 is a reply to message #625787] Tue, 14 October 2014 06:25 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
 Select  decode(grade, 'E', '33-39',
  2                  'D', '40-49',
  3             'C', '50-59',
  4             'B', '60-69',
  5             'A', '70-79',
  6             'A+','80% and above'
  7          ),count(tstuid) from test1
  8  group by  decode(grade, 'E', '33-39',
  9                  'D', '40-49',
 10             'C', '50-59',
 11             'B', '60-69',
 12             'A', '70-79',
 13             'A+','80% and above'
 14         )
/

my grade is calculation of formula column.
function GRADEFormula return Char is
begin
  IF :CF_1>= 80 THEN
  	 RETURN('A+');
  ELSIF :CF_1 >= 70 AND :CF_1 <= 79 THEN
  	RETURN('A');
  ELSIF :CF_1 >= 60 AND :CF_1 <= 69 THEN
  	RETURN('B');
  ELSIF :CF_1 >= 50 AND :CF_1 <= 59 THEN
  	RETURN('C');
  ELSIF :CF_1 >= 40 AND :CF_1 <= 49 THEN
  	RETURN('D');
  ELSIF :CF_1 >= 33 AND :CF_1 <= 39 THEN
  	RETURN('E');
  ELSE 
  	RETURN('F');
  	END IF;
end;

please advised.
Re: Count (A+,A,B) from grade column [message #625807 is a reply to message #625804] Tue, 14 October 2014 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Advise on what? You appear to have fixed the obvious mistake.
Re: Count (A+,A,B) from grade column [message #625808 is a reply to message #625807] Tue, 14 October 2014 06:58 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
but it struck on "80% and above". the other codes are not displayed on.
Re: Count (A+,A,B) from grade column [message #625809 is a reply to message #625808] Tue, 14 October 2014 07:05 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if the select above always gives "80% and above" then grade must always be A+ in test1
Re: Count (A+,A,B) from grade column [message #625810 is a reply to message #625809] Tue, 14 October 2014 07:08 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
In fact does test1 even have a grade column? It doesn't in your other thread.
Rememeber we don't know anything about your tables or your report other than what you tell us.
We don't know how grade is calculated or where it's stored.
Re: Count (A+,A,B) from grade column [message #625816 is a reply to message #625810] Tue, 14 October 2014 07:42 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 TID                                       NOT NULL NUMBER(9)
 TDATE                                     NOT NULL DATE
 CLASS                                              VARCHAR2(30)
 EXAMC                                              VARCHAR2(80)
 SUBJECT                                            VARCHAR2(70)
 TOTMARKS                                           NUMBER(3)
 SEC                                                VARCHAR2(50)

SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 TID                                                NUMBER(9)
 TDATE                                              DATE
 TSTUID                                             NUMBER(7)
 OBTMARKS                                           NUMBER(5,2)
 PERCT                                              NUMBER(6,2)
 REMARK                                             VARCHAR2(300)


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



function GRADEFormula return Char is
begin
  IF :CF_1>= 80 THEN
  	 RETURN('A+');
  ELSIF :CF_1 >= 70 AND :CF_1 <= 79 THEN
  	RETURN('A');
  ELSIF :CF_1 >= 60 AND :CF_1 <= 69 THEN
  	RETURN('B');
  ELSIF :CF_1 >= 50 AND :CF_1 <= 59 THEN
  	RETURN('C');
  ELSIF :CF_1 >= 40 AND :CF_1 <= 49 THEN
  	RETURN('D');
  ELSIF :CF_1 >= 33 AND :CF_1 <= 39 THEN
  	RETURN('E');
  ELSE 
  	RETURN('F');
  	END IF;
end;
Re: Count (A+,A,B) from grade column [message #625820 is a reply to message #625816] Tue, 14 October 2014 07:58 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Trouble here is you've given us some formula columns, but not all of them and we don't know how they relate to the main report query.
If all the calculations were in the main report query then you could just post that and we could see what you're doing.
They're not though, so you're going to have to write some sentences to explain the relationship.

This can be done in a single query - and should, formula and summary columns won't make it any easier.

It will be of the form:
SELECT grade, count(*)
FROM (select student_id, <calculation of grade>
      from .....
      where .....
     )
group by grade


Re: Count (A+,A,B) from grade column [message #625824 is a reply to message #625820] Tue, 14 October 2014 08:07 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
please see the attached file.its a matrix with group report. master Group class+Section and then subject and toal marks of subjects and then studentid,name and obtained marks.
Grade, Cf_1 are the formula columns and Pos, perc is the view which i created with your help.
/forum/fa/12226/0/
  • Attachment: 33434.JPG
    (Size: 45.46KB, Downloaded 577 times)
Re: Count (A+,A,B) from grade column [message #625825 is a reply to message #625824] Tue, 14 October 2014 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
More sentences, less pictures please. We had this conversation on the last reports thread.
What is Sumobtmarks and Sumtotmarks (which doesn't appear to be in the above screenshot).

But really you should be able to do this. You had to work out percentage per student in the last thread.
Do the same again (in a single query), convert percentage to grade, then apply it to my example structure above.
Re: Count (A+,A,B) from grade column [message #625839 is a reply to message #625825] Tue, 14 October 2014 21:31 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
select distinct :grade,count(*) from 
(select stuid,:grade,examc,test.class,sec
 from student,dual,test,test1
where student.status='PRESENT'
and test.class=:cls --(pick through Parameter Form)
and test.sec=:sess  --(pick through Parameter Form)
and test.examc=:ty  --(pick through Parameter Form)
group by :grade,stuid,examc,test.class,sec)

this query shows grade as null and count is equal to 0.grade is based on if condition as described in previous post.i want to show the grades on left and in each subject i want to count no of A+, A, B, C....and so on.
Re: Count (A+,A,B) from grade column [message #625866 is a reply to message #625839] Wed, 15 October 2014 02:52 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't use a bind variable like that. It'll only have one value for any given run of the query which is not what you need.
You need to calculate grade in the query, not try and refer to a pre-calculated grade item in the report.
Re: Count (A+,A,B) from grade column [message #625907 is a reply to message #625866] Thu, 16 October 2014 00:02 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
With case in select statement how i can use this calculated grade. please give me some tip/help.
Re: Count (A+,A,B) from grade column [message #625915 is a reply to message #625907] Thu, 16 October 2014 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
SELECT grade, count(*)
FROM (select student_id, 
             CASE WHEN percentage >= 80 THEN 'A+'
                  WHEN percentage >= 70 THEN 'A'
                  .....
             END AS grade        
      from (SELECT student_id, SUM(obtmarks)/SUM(totmarks) AS percentage
            FROM ....
            WHERE .....
            GROUP BY student_id
           )
     )
group by grade


Now spend some time thinking about it and fill in the blanks. It's not difficult.
Re: Count (A+,A,B) from grade column [message #626025 is a reply to message #625915] Sun, 19 October 2014 10:44 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
 SELECT grade, count(*)
 FROM (select stuid,
              CASE WHEN perc >= 80 THEN 'A+'
                   WHEN perc between 70 and 79 THEN 'A'
                   WHEN perc between 60 and 69 THEN 'B'
                   WHEN perc between 50 and 59 THEN 'C'
                   WHEN perc between 40 and 49 THEN 'D'
                   WHEN perc between 33 and 39 THEN 'E'
                   WHEN perc < 33  THEN 'F'
              END AS grade
       from (SELECT stuId, SUM(obtmarks)/SUM(totmarks)*100 AS perc,EXAMC,SEC,TEST.CLASS
             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 examc=:TY
        group by TEST.class,examc,sec,stuid)
             GROUP BY stuid,perc
            )
 group by grade
Out put is:
SQL> /

GR   COUNT(*)
-- ----------
A           1
A+          1
B           5
C           8
D           2
E           3
F           2
            3

i want to count Also each subject grade please advised ...
Re: Count (A+,A,B) from grade column [message #626043 is a reply to message #626025] Mon, 20 October 2014 02:39 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
What exact output do you want?
Re: Count (A+,A,B) from grade column [message #626067 is a reply to message #626043] Mon, 20 October 2014 06:04 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
/forum/fa/12231/0/
That's the output i required.
  • Attachment: 23.JPG
    (Size: 15.06KB, Downloaded 535 times)

[Updated on: Mon, 20 October 2014 06:05]

Report message to a moderator

Re: Count (A+,A,B) from grade column [message #626073 is a reply to message #626067] Mon, 20 October 2014 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So add subject to all the selects and group bys.
Re: Count (A+,A,B) from grade column [message #626716 is a reply to message #626073] Fri, 31 October 2014 10:39 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Sir
it shows the A (no of record of A+) and A+ (no of record of A).
SELECT grade, count(*)
 FROM (select stuid,
              CASE WHEN perc >= 80 THEN 'A+'
                   WHEN perc between 70 and 79 THEN 'A'
                   WHEN perc between 60 and 69 THEN 'B'
                   WHEN perc between 50 and 59 THEN 'C'
                   WHEN perc between 40 and 49 THEN 'D'
                   WHEN perc between 33 and 39 THEN 'E'
                   WHEN perc < 33  THEN 'F'
              END AS grade
       from (SELECT stuId, SUM(obtmarks)/SUM(totmarks)*100 AS perc,EXAMC,SEC,TEST.CLASS
             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 examc=:TY
        group by TEST.class,examc,sec,stuid)
             GROUP BY stuid,perc
            )
 group by grade
Re: Count (A+,A,B) from grade column [message #626827 is a reply to message #626716] Mon, 03 November 2014 23:52 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
Waiting for your reply.
Re: Count (A+,A,B) from grade column [message #626857 is a reply to message #626827] Tue, 04 November 2014 03:29 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you saying that the A+ row gives the count of students who got A and the A row gives the count of students that got A+?
If that's happening then most likely it's because your joins are wrong.
Or the above query is right and whatever you are comparing it to is wrong.

The obvious thing to do is remove the outer layer of the select so that you get stuid and grade. Then check if the students have the correct grade.
Previous Topic: REP-0820 cannot import image
Next Topic: pdf report format issue
Goto Forum:
  


Current Time: Sat Jul 04 16:43:11 CDT 2020