Multiple field report, with custom counts in MySql

Question

Hey all,

I am using a system I didn't create. The system has 3 main tables:

users, courses, and usergroups. I am using an extra table called coursehits.

It's a MySQL DB, 5.0. There aren't any relationships in the DB, so users are assigned to courses by simply adding an entry to usergroups (course_id and user_id) from the courses and users table. Likewise if they start a course an entry is made to coursehits.

I am trying to count the number of users in coursehits and usergroups for reporting data. So far I have the following which doesn't count correctly in one query but this doesn't count correctly, the results are much too high:

SELECT DISTINCT
c.course_name,
COUNT(ug.user_id) AS "Enrolled",
COUNT(ch.user_id) as "Started"

FROM courses c, usergroups ug, coursehits ch, users u

WHERE ch.user_id = u.id
AND ug.user_id = u.id
AND ug.course_id = c.id
AND ch.page_id != 4
GROUP BY 1

Before I was doing the following individually which does work:

SELECT DISTINCT c.course_name, COUNT(ug.user_id) AS "Enrolled"
FROM courses c, usergroups ug
WHERE ug.course_id = c.id
GROUP BY 1

Where as now I am trying to report the status of users for each course on one query, I hope that makes sense?!?

Answer 1

Try:

SELECT c.course_name,
       COUNT(DISTINCT ug.user_id) AS "Enrolled",
       COUNT(DISTINCT ch.user_id) as "Started"
FROM courses c
left join usergroups ug on ug.course_id = c.id
left join coursehits ch on ch.course_id = c.id and ch.page_id != 4
GROUP BY c.course_name