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

Asked By 110 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by Ravi_A
Answered By 0 points N/A #89163

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

qa-featured

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.

Best Answer
Best Answer
Answered By 0 points N/A #89164

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

qa-featured

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.

Answered By 110 points N/A #89165

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

qa-featured

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?

Answered By 0 points N/A #89166

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

qa-featured

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.

Answered By 110 points N/A #89167

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

qa-featured

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.

Login/Register to Answer

Related Questions