Home » Developer & Programmer » Reports & Discoverer » Count (A+,A,B) from grade column (6i,Oracle,9i Rel.2)
Count (A+,A,B) from grade column Mon, 13 October 2014 13:33
 shahzad-ul-hasan Messages: 600Registered: 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.
• Attachment: 1322.JPG
Re: Count (A+,A,B) from grade column [message #625739 is a reply to message #625737] Mon, 13 October 2014 13:37
 Littlefoot Messages: 21736Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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
 shahzad-ul-hasan Messages: 600Registered: 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
 cookiemonster Messages: 13894Registered: 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
 shahzad-ul-hasan Messages: 600Registered: 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;
```

Re: Count (A+,A,B) from grade column [message #625807 is a reply to message #625804] Tue, 14 October 2014 06:50
 cookiemonster Messages: 13894Registered: 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
 shahzad-ul-hasan Messages: 600Registered: 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
 cookiemonster Messages: 13894Registered: 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
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
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
 shahzad-ul-hasan Messages: 600Registered: 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;
```

```
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
 cookiemonster Messages: 13894Registered: 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 .....
)
```

Re: Count (A+,A,B) from grade column [message #625824 is a reply to message #625820] Tue, 14 October 2014 08:07
 shahzad-ul-hasan Messages: 600Registered: 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.
• Attachment: 33434.JPG
Re: Count (A+,A,B) from grade column [message #625825 is a reply to message #625824] Tue, 14 October 2014 08:34
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
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
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
```select distinct :grade,count(*) from
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)
```

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
 cookiemonster Messages: 13894Registered: 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
 shahzad-ul-hasan Messages: 600Registered: 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
 cookiemonster Messages: 13894Registered: 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'
.....
from (SELECT student_id, SUM(obtmarks)/SUM(totmarks) AS percentage
FROM ....
WHERE .....
GROUP BY student_id
)
)
```

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
 shahzad-ul-hasan Messages: 600Registered: 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'
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
)
Out put is:
SQL> /

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

Re: Count (A+,A,B) from grade column [message #626043 is a reply to message #626025] Mon, 20 October 2014 02:39
 cookiemonster Messages: 13894Registered: 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
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member

That's the output i required.
• Attachment: 23.JPG

[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
 cookiemonster Messages: 13894Registered: 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
 shahzad-ul-hasan Messages: 600Registered: 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'
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
)
```
Re: Count (A+,A,B) from grade column [message #626827 is a reply to message #626716] Mon, 03 November 2014 23:52
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member