how do I count this?

I have three tables:

BS_REGISTRATION
-----------------
REG_ID (PK)
REG_ATTEND
SESSION_ID (FK)

BS_SESSION
------------
SESSION_ID (PK)
SESSION_DATE
COURSE_ID (FK)

BS_COURSE
-----------
COURSE_ID (PK)
COURSE_NAME

I would like to find out the count of registration sorted by course. e.g.

COURSE1 34
COURSE2 12

SELECT c.COURSE_NAME, count(REG_ID) from BS_SESSION s, BS_REGISTRATION r, BS_COURSE c
WHERE s.SESSION_ID = r.SESSION_ID
AND s.COURSE_ID = c.COURSE_ID
AND TO_DATE(s.SESSION_DATE) BETWEEN TO_DATE('12-03-2003','DD-MM-YYYY') AND TO_DATE('31-03-2004','DD-MM-YYYY')
AND r.REG_ATTEND='attended'
GROUP BY COURSE_NAME

I know if I do the above I will get an error. But how do I do this query? Thank you so much.

You did not write what the error message is, that you get, but if SESSION_DATE is already defined as a date column you don't need the TO_DATE(s.SESSION_DATE) (and I guess it will give an error)

I think

AND s.SESSION_DATE BETWEEN TO_DATE('12-03-2003','DD-MM-YYYY') AND TO_DATE('31-03-2004','DD-MM-YYYY')

should work (didn't test it though)

just as a side note...the left side "to_date" function will also hurt your performance. If the field is a date field already I would definatley take that out.

~jimbag8