Returning a 0 on an outer join count 1

[notadba]

Hi,

I have written a query that looks courses and the course sessions that occur in the next 'x' period. I have added a left outer join to the session enrolment table so that I can look at the number of enrolled students for any session - or courses that have no enrolments

This is ok - except I am returning a count of 1 for those sessions that have no enrolments. How can I get this to give me a 0 instead?

SQL looks like :
SELECT C.DESCR
, A.COURSE
, A.COURSE_START_DT
, A.COURSE_END_DT
, A.MIN_STUDENTS
, A.MAX_STUDENTS
, COUNT(*)
FROM CRSE_SESSN_TBL A LEFT OUTER JOIN TRN_ENROLL_VW B ON A.COURSE = B.COURSE AND A.SESSION_NBR = B.SESSION_NBR
, COURSE_TBL C
WHERE A.COURSE_START_DT BETWEEN SYSDATE AND '2006-12-31'
AND A.COURSE = C.COURSE
GROUP BY C.DESCR, A.COURSE, A.COURSE_START_DT, A.COURSE_END_DT, A.MIN_STUDENTS, A.MAX_STUDENTS

The Count(*) returns a 1.

Any ideas?

Thanks

 

[taupirho]

The answer to your question is .... count(*) -1

This is probably not the answer you wanted, maybe you need to re-state the question - and provide some sample data perhaps ??

 

[feherke]

Hi

See the below example, I think you need something like my last [tt]select[/tt]'s last column.

[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] first;

        ID NAME
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] second;

        ID NAME
---------- ----------
         1 one
         3 three
         3 three b
         4 four
         4 four b
         4 four c
         4 four d

7 rows selected.

[blue]SQL>[/blue] [b]select[/b] f.id,f.name,count(*),[red]sum(nvl2(s.id,1,0))[/red] [b]from[/b] first f [b]left join[/b] second s [b]on[/b] s.id=f.id [b]group by[/b] f.id,f.name;

        ID NAME         COUNT(*) SUM(NVL2(S.ID,1,0))
---------- ---------- ---------- -------------------
         1 one                 1                   1
         2 two                 1                   [red]0[/red]
         3 three               2                   2
         4 four                4                   4
         5 five                1                   [red]0[/red]

Feherke.

http://rootshell.be/~feherke/

 

[notadba]

Thanks for your responses - have not had a chance to look at the fix since I have been on DB2 and SQL server site since posting this.

The first count(*) -1 is so simple - and worked perfectly. Will try the other approach as time allows.

 

[taupirho]

Thanks for the star notadba, however my answer was a bit flippant in as much as there may be times when you have a genuine count of 1 (see feherke's example - row 1 as opposed to rows 2 and 5 in the final table). You certainly won't want to subtract 1 from genuine counts of 1. So how do you determine what is genuine or not?? - that's when you may have to implement something like feherkes solution