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