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.
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.
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.
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?
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.
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.
