Home » Other » Training & Certification » problem with gp by function
problem with gp by function [message #272127] Thu, 04 October 2007 01:49 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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 Go to previous messageGo to next message
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 #272175 is a reply to message #272157] Thu, 04 October 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand your problem and my answer is still the same, re-read it.

Regards
Michel
Re: problem with gp by function [message #272365 is a reply to message #272127] Thu, 04 October 2007 13:46 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Oracle is a database, not a mind reader.

Michel is right (as usual Laughing ) you should create a static table with your grades in them and do an outer join to get your desired counts.

Re: problem with gp by function [message #272405 is a reply to message #272365] Thu, 04 October 2007 23:12 Go to previous messageGo to next message
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 #272408 is a reply to message #272127] Thu, 04 October 2007 23:37 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
create a lookup table for grade and left join it to student table.
Re: problem with gp by function [message #272434 is a reply to message #272408] Fri, 05 October 2007 01:05 Go to previous message
Michel Cadot
Messages: 64111
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
Previous Topic: Date Problem
Next Topic: 1z0-147 Brain Dumps
Goto Forum:
  


Current Time: Mon Dec 05 23:56:29 CST 2016

Total time taken to generate the page: 0.10096 seconds