| problem with gp by function [message #272127] |
Thu, 04 October 2007 01:49  |
orarep
Messages: 56
Registered: September 2007
|
Member |
|
|
hello
my prob is:
i have a table regarding academic grades of student i.e A+,A,B+,B etc and i want to know the count of different grades
now my select statement is
"select grd,count(grd) grades from std_tab group by grd;"
my problem is that if e.g A or A+ grade =0 in the table so this statment do not show it, what i want that it shows "zero" 0 if that specific grade is not there i.e count is zero e.a
A+ 0
A 10
B+ 8
B 70
etc etc
i applied the following
"select grd,nvl(count(grd),0) grades from std_tab group by grd;"
but still i do not receive 0 for no grades
plz any one can guide
regards
|
|
|
|
| Re: problem with gp by function [message #272136 is a reply to message #272127] |
Thu, 04 October 2007 02:02  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You can't do it directly.
How can Oracle know the name of the grades that are not in the table?
It can't.
So you have to give him the list from, for instance, another table that lists all the valid grade values, then outer join both tables.
By the way:
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
Regards
Michel
[Updated on: Thu, 04 October 2007 02:02] Report message to a moderator |
|
|
|
| Re: problem with gp by function [message #272157 is a reply to message #272136] |
Thu, 04 October 2007 02:31 |
orarep
Messages: 56
Registered: September 2007
|
Member |
|
|
thanks Mr.michel for correcting me , i will adhere to the rules.
i have oracle 8.1.7 with developer 6.0
my table have the following structure
std_tab(name,course,ob_mks,grade)
now garde col can have only the follwoing values
A+,A,B+,B,C+,C,D
now my query is that how many are getting A+,A,B+,B,C+,C,D i.e
"select grade,count(grade) grade from std_tab group by grade"
now problem is that if e.g no student secure A+ so this query do not display it , what i want it display it as 0 not to just skip it i.e like following
A+ 0
A 9
B+ 30
...
thanks
so plz help me what should i do.
|
|
|
|
|
|
|
|
| Re: problem with gp by function [message #272405 is a reply to message #272365] |
Thu, 04 October 2007 23:12 |
orarep
Messages: 56
Registered: September 2007
|
Member |
|
|
well yes, my mistake
i was just bogged down in this query so i didn't see it.
Does static tables mean, that i should create a separate table or view for every grade i.e 6 tables and then apply following query on each table i.e
"select nvl(count(grade),0) gardes from std_tab1
and these tables will be creatd dynamically etc
sir am i write or not
plz reply
thanks
|
|
|
|
|
|
| Re: problem with gp by function [message #272434 is a reply to message #272408] |
Fri, 05 October 2007 01:05 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
How is this adding something to the previous posts?
We know you are able to post the same things than others, you already showed it in your many previous posts.
Now start to answer questions that have been answered or try to say something new.
Otherwise it is useless and just a waste of space.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) you should create a static table with your grades in them and do an outer join to get your desired counts.