Valid in a particular student’s case or it is NULL

Asked By sameer 110 points N/A Posted on - 04/28/2011

Hello everyone,

I have a table student where other that the student name, student id, subjects, marks details, I also store whether the student has some salary or not. This last field stores the salary amount if its valid in a particular student's case or it is NULL. Any pointers on this will be highly appreciated.

• Status: Open
• Question Views: 858
• Answer Count: 5
• Vote Up 11 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Ravi_A

Go To Solution

Answered By Ravi_A 0 points N/A #89163

Valid in a particular student’s case or it is NULL

You can use the UNION set function to achieve this result:

select count(*) cntr, 'Non-Sal' Type
from student
where salary is null
  union all
select count(*) cntr, 'Sal' Type
from student
where salary is not null;

This is a straightforward query displaying the number of students that fall under the Non-salary and salary type. The only contraint with this query is that for tables with many records this query won't yield good performance.

About Ravi_A

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Best Answer

Best Answer

Answered By Ravi_A 0 points N/A #89164

Valid in a particular student’s case or it is NULL

I just tried another query which was giving a better performance using case statement :

select sum(case when salary IS NULL then 1 else 0 end) Non-Sal,
       sum(case when salary IS NOT NULL then 1 else 0 end) Sal
from student;

This query instead of using a count function sums up the salaried and non-salaried student based on the case expression whether the salary field is NULL or NOT NULL.

About Ravi_A

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By sameer 110 points N/A #89165

Valid in a particular student’s case or it is NULL

Thanks for providing two options for the same output. Can you give me another way of framing the above query with count function? I know union function will lead to performance issues which I would rather avoid. So it possible to have another query with count function?

About sameer

Questions
2

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By Ravi_A 0 points N/A #89166

Valid in a particular student’s case or it is NULL

I missed a very basic function while responding to your question. Take a look at the following query and you will realise that this is the easiest possible solution to the above problem :

select count( salary ) Non-Sal, count( case when salary is null then 1 end ) Sal
  from student;

count( salary ) – will count all the not null fields
and count with the case expression explicitly mentions counting salary field which is NULL.

About Ravi_A

Questions
0

Answers
3

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database

Answered By sameer 110 points N/A #89167

Valid in a particular student’s case or it is NULL

Yes I agree the last option is the simplest and the easiest to pick up. I really appreciate your effort in coming up with so many solutions.

About sameer

Questions
2

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/28/2011
• Question Category: Oracle Database