Help with SQL Query

Hello all. I have a query which is very simple but I just cant think of a way to do it. I have 3 tables
enrollment course
-------- ----
enrollment_id course_id
course_id course_title
ecommerce_time
enrollment_status_id
A value of 1 for enrollment_status_id = 1 means that the status is complete. Now in this query I need to get a list of all courses, a count of all the enrollments for that course_id and a count of all completed courses(enrollment_status_id=1). And then the condition is a start date and an end date(inputs). The query must be filtered on the condition
ecommerce_time >= start_date and ecommerce_time >= end_date. How do I get the 2 different counts which have different conditions? Thank you.
I have this query but I want to exclude the results which have 0 enrollments from the result set.
Select course_id, course_title As course_title,
(Select count(enrollment_id) From enrollment Where enrollment.course_id = course.course_id
And (ecommerce_time >= '01/01/2000' And ecommerce_time <= '03/01/2005')) As total_enrollments,
(Select count(enrollment_results_id) From enrollment Where enrollment_results_id = '1'
And enrollment.course_id = course.course_id And (ecommerce_time >= '01/01/2000' And ecommerce_time <= '03/01/2005')) As total_completions
From course Where organization_id = '1'
Order By course_titlePersonally, this is how I would form that query:
SELECT
course_id,
course_title,
TE.total_enrollments,
E.total_completions
FROM
course
INNER JOIN
(SELECT course_id, COUNT(*)AS total_enrollments FROM enrollment
WHERE ecommerce_time >= '01/01/2000' AND ecommerce_time <= '03/01/2005
GROUP BY course_ID) AS TE ON course.course_id = TE.course_id
LEFT OUTER JOIN
(SELECT course_id, COUNT(*)AStotal_completionsFROM enrollment
WHERE enrollment_results_id = '1' AND
ecommerce_time >='01/01/2000' AND ecommerce_time <= '03/01/2005'
GROUP BYenrollment.course_id) AS E ON course.course_id =E.course_id
WHERE
organization_id = '1' AND
TE.total_enrollments > 0
ORDER BY
course_title

The main thing here is using a derived table and to INNER JOIN onit. I also moved your other query as a LEFT OUTER JOIN although Ithink the query would have worked without moving it. To me thisapproach is easier to read and understand. I am not 100% sureabout performance.