Displaying records with a column value 0 when there is no record

Question

I have following tables:

enter image description here

Schema

tbl_semester_empid {id, semester_name varchar(50), start_date}

tbl_batch_empid {id, semester_id,batch_name, session_room}

tbl_associate_empid {id, associate_name, batch_id, contact, joining_date, induction_result, stream_result int}

The problem query is: Display semester wise associate count.

I have wrote 3 queries:

--basic query
SELECT s.ID "Semester Id", COUNT(*) "Associate Count" FROM TBL_SEMESTER_593932 s, TBL_ASSOCIATE_593932 a, TBL_BATCH_593932 b 
        WHERE s.ID=b.SEMESTER_ID AND a.BATCH_ID=b.ID  
                GROUP BY s.ID;

--used NVL function
SELECT s.ID "Semester Id", NVL(COUNT(*),0) "Associate Count" FROM TBL_SEMESTER_593932 s, TBL_ASSOCIATE_593932 a, TBL_BATCH_593932 b 
        WHERE s.ID=b.SEMESTER_ID AND a.BATCH_ID=b.ID  
                GROUP BY s.ID;

-- used LEFT OUTER JOIN and  JOIN
SELECT s.ID "Semester Id", NVL(COUNT(*),0) "Associate Count" 
        FROM TBL_SEMESTER_593932 s LEFT OUTER JOIN TBL_BATCH_593932 b ON s.ID=b.SEMESTER_ID 
             JOIN TBL_ASSOCIATE_593932 a ON a.BATCH_ID=b.ID  
                   GROUP BY s.ID;

All of them display following output (it shows records corresponding to only those semesters which have at least 1 student in it, if theis is no student in that semester, no information for that semester is displayed):

enter image description here

I want the semesters with no associates in them as 0 in Associates Count column. So I tried the same with two LEFT OUTE JOINs:

SELECT s.ID "Semester Id", NVL(COUNT(*),0) "Associate Count" 
    FROM TBL_SEMESTER_593932 s LEFT OUTER JOIN TBL_BATCH_593932 b ON s.ID=b.SEMESTER_ID 
         LEFT OUTER JOIN TBL_ASSOCIATE_593932 a ON a.BATCH_ID=b.ID  
                GROUP BY s.ID;

However the result is weird:

enter image description here

bonCodigo · Answer 1 · 2013-01-09T06:57:08.390

1

Have you tried using CASE when ?

According to the data in your associate table, there are records for all batches/semesters.

So try this, well your NVL is similar to case...

select s.id, s.name, CASE WHEN count(a.id) IS NULL THEN 0 ELSE count(a.id) END as ACount
from tblsem s
left join tblbatch b
on b.sid = s.id
left join tblaso a
on b.id = a.batchid
group by s.id, s.name
;

Results:

ID  NAME    ACOUNT
1   sem1    4
2   sem2    3
3   sem3    0

SQLFIDDLE DEMO

edited Jan 09 '13 at 06:57

answered Jan 09 '13 at 06:31

bonCodigo

14,268
1
48
91

added Schema, just see original question, will try CASE when, and update – Mahesha999 Jan 09 '13 at 06:33
not able to think of how should I use CASE, a good example is [here](http://msdn.microsoft.com/en-us/library/ms181765.aspx), but still not able to – Mahesha999 Jan 09 '13 at 06:37
@Mahesha999 forget about case. :) coz Oracle NVL does the same.. but try thsi out. I will also provide you with some other info as well. – bonCodigo Jan 09 '13 at 06:47
@Mahesha999 please take a look at the sqlfiddle reference and comment. I used some sample data of my own to show you the logic. In my sql you can use `coalesce` function which does same as NVL in oracle, however I used case ;) – bonCodigo Jan 09 '13 at 06:58

alzaimar · Accepted Answer · 2013-01-09T07:42:53.003

1

Basically, what you have to do is to use a list of all semester available and then calculate the amount for each one:

select s.ID, 
       s.Semester_Name,
       NVL ((select count(*) 
          from TBL_ASSOCIATE_593932 a
               join TBL_BATCH_593932 b 
                 on a.BATCH_ID=b.ID
        where  s.ID=b.SEMESTER_ID
       ),0) as Amount
 from TBL_SEMESTER_593932 s

another approach would be left joining your list of all semesters and then count

select s.ID, 
       s.Semester_Name,
       count(*) as Amount 
 from TBL_SEMESTER_593932 s
        left join (
          TBL_BATCH_593932 b
          join TBL_ASSOCIATE_593932 a  
            on a.BATCH_ID=b.ID)
           on s.ID=b.SEMESTER_ID

group by s.ID, s.Semester_Name
order by s.ID

[Edit]: The last query was corrected according to @Mahesha999.

edited Jan 09 '13 at 07:42

answered Jan 09 '13 at 06:43

alzaimar

4,572
1
16
30

First query was awesome and it is working the way it is desired, but the third one is I think not well formed, it should be `from TBL_SEMESTER_593932 s left join **TBL_BATCH_593932 b** on s.ID=b.SEMESTER_ID join **TBL_ASSOCIATE_593932 a** on a.BATCH_ID=b.ID`. However this becomes similar to my last query and does not show records for semesters with no students in them – Mahesha999 Jan 09 '13 at 06:57
@Mahesha999: Thank you for showing me the error: I corrected the query accordingly. – alzaimar Jan 09 '13 at 07:08
but it is still not showing the records for semesters with no associated in them :( – Mahesha999 Jan 09 '13 at 07:38
Well, I implemented what you suggested. Anyway, try this approach. I used braces to make sure that all semesters a listed. – alzaimar Jan 09 '13 at 07:43
Also you dont need to use NVL here, logically - just for performance, am I right? @ alzaimar Thanks for that first query anyways – Mahesha999 Jan 09 '13 at 12:39
Well, NVL converts a NULL count to a 0 count. For the second query, I am most likely just to stupid to get it right. Anyway, the first one helps and I'm happy to help. – alzaimar Jan 09 '13 at 20:44

Displaying records with a column value 0 when there is no record

2 Answers2