CODEWITHSUNDEEP
mysqlsumsubquery
Kimber Warden
Kimber Warden

Reputation: 193

COUNT in a subquery, then SUM

This should be relatively simple, but I haven't been able to figure it out.

I have a query that currently creates a table that looks like:

Teacher,  course,    # students in course
John Doe, Algebra 1,      3
John Doe, Algebra 2,      1
Jeff Doh, Geometry,       2

I want to also count the number of students being taught by each teacher, giving results like:

Teacher,  course,     # students in course, # students with teacher
John Doe, Algebra 1,             3,               4
John Doe, Algebra 2,             1,               4
Jeff Doh, Geometry,              2,               2

But I can't figure out how to produce the last column that sums all of the students being taught by a teacher across all courses.

Here's my current query (I'd also be interested in a better way to write this existing query)

SELECT
u.username AS 'teacher',
c.fullname AS 'course',
(SELECT COUNT(u1.username)
       FROM prefix_user u1
        JOIN prefix_user_enrolments ue1 on ue1.userid=u1.id
        JOIN prefix_enrol e1 ON e1.id=ue1.enrolid
        JOIN prefix_course c1 on c1.id = e1.courseid
        JOIN prefix_context AS ctx1 ON ctx1.instanceid = c1.id
        JOIN prefix_role_assignments AS ra1 ON ra1.contextid = ctx1.id     
        JOIN prefix_course_categories AS cc1 ON cc1.id=c1.category
       WHERE ra1.roleid="5" ### "5" = student
        AND ra1.userid=u1.id
        AND e1.courseid=c1.id
        AND c1.id=c.id) AS '# students in course'
FROM prefix_user u
JOIN prefix_user_enrolments ue on ue.userid=u.id
JOIN prefix_enrol e ON e.id=ue.enrolid
JOIN prefix_course c on c.id = e.courseid
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_course_categories AS cc ON cc.id=c.category
WHERE ra.roleid="3" ### "3" = Teacher

GROUP BY c.id
ORDER BY cc.name, c.fullname

I wish I could just add a SUM(# students in course) column, but that doesn't work. And the interface I'm using won't let me use WITH ROLLUP.

My Schema:

CREATE TABLE prefix_user
    (`id` varchar(2), `username` varchar(11))
;

INSERT INTO prefix_user
    (`id`, `username`)
VALUES
    ('1', 'JohnDoe'),
    ('2', 'JaneDuh'),
    ('3', 'JeffDoh'),
    ('4', 'JackSprat'),
    ('5', 'WillieWinky'),
    ('6', 'DonaldDuck'),
    ('7', 'MickeyMouse')
;

CREATE TABLE prefix_user_enrolments
    (`id` varchar(2), `enrolid` varchar (4), `userid` varchar(1), `status` varchar(1))
;

INSERT INTO prefix_user_enrolments
    (`id`, `enrolid`, `userid`, `status`)
VALUES
    ('10', '1000', '1', '0'),
    ('11', '1001', '2', '0'),
    ('12', '2000', '3', '0'),
    ('13', '1002', '4', '0'),
    ('14', '2001', '5', '0'),
    ('15', '1003', '6', '0'),
    ('16', '2002', '7', '0'),
    ('17', '3000', '1', '0'),
    ('18', '3001', '7', '0')
;

CREATE TABLE prefix_enrol
    (`id` varchar(4), `status` varchar (1), `courseid` varchar(3), `roleid` varchar(1))
;

INSERT INTO prefix_enrol
    (`id`, `status`, `courseid`, `roleid`)
VALUES
    ('1000', '0', '100', '5'),
    ('1001', '0', '100', '5'),
    ('2000', '0', '200', '5'),
    ('1002', '0', '100', '5'),
    ('2001', '0', '200', '5'),
    ('1003', '0', '100', '3'),
    ('2002', '0', '200', '3'),
    ('3000', '0', '300', '3'),
    ('3001', '0', '300', '5')
;

CREATE TABLE prefix_course
    (`id` varchar(3), `fullname` varchar(8), `category` varchar(2))
;

INSERT INTO prefix_course
    (`id`, `fullname`, `category`)
VALUES
    ('100', 'Algebra1', '10'),
    ('200', 'Geometry', '10'),
    ('300', 'Algebra2', '10')
;

CREATE TABLE prefix_context
    (`id` varchar(5), `instanceid` varchar(8))
;

INSERT INTO prefix_context
    (`id`, `instanceid`)
VALUES
    ('10000', '100'),
    ('10001', '100'),
    ('20000', '200'),
    ('10002', '100'),
    ('20001', '200'),
    ('10003', '100'),
    ('20002', '200'),
    ('30000', '300'),
    ('30001', '300')
;

CREATE TABLE prefix_role_assignments
    (`id` varchar(6), `roleid` varchar(1), `contextid` varchar(5), `userid` varchar(1))
;

INSERT INTO prefix_role_assignments
    (`id`, `roleid`, `contextid`, `userid`)
VALUES
    ('100000', '5', '10000', '1'),
    ('100001', '5', '10001', '2'),
    ('200000', '5', '20000', '3'),
    ('100002', '5', '10002', '4'),
    ('200001', '5', '20001', '5'),
    ('100003', '3', '10003', '6'),
    ('200002', '3', '20002', '7'),
    ('300000', '3', '30000', '1'),
    ('300001', '5', '30001', '7')
;

CREATE TABLE prefix_role
    (`id` varchar(1), `shortname` varchar(7))
;

INSERT INTO prefix_role
    (`id`, `shortname`)
VALUES
    ('5', 'student'),
    ('3', 'teacher')
;

CREATE TABLE prefix_course_categories
    (`id` varchar(2), `name` varchar(4))
;

INSERT INTO prefix_course_categories
    (`id`, `name`)
VALUES
    ('10', 'math')
;

Upvotes: 2

Views: 129

Answers (1)

James
James

Reputation: 124

It looks like your original query may be in error. I believe JohnDoe is enrolled in Algebra 1 as a student not a teacher. I also noticed that the role is held on two tables: prefix_enrol and prefix_role_assignments - not sure if I am misinterpreting something or this is redundant data. In my attempt below I used sub-queries to count the enrollments by courseID where the role of the enrolled is a student and count the students per teacher. There may be a more efficient way to do this, but this is what I cam up with on my first attempt:

SELECT
 t.username AS 'teacher'
 ,c.fullname AS 'course'
 ,studentCountCourse.numStudents AS '# students in course'
 ,studentCountTeach.numStudents AS '# students with teacher'
FROM prefix_enrol AS e
INNER JOIN prefix_course AS c ON c.id = e.courseid
INNER JOIN prefix_user_enrolments AS ue ON ue.enrolid = e.id
INNER JOIN prefix_user AS t ON t.id = ue.userID AND e.roleid = 3
INNER JOIN (
 SELECT
  e1.courseid
  ,count(e1.courseid) AS numStudents
 FROM prefix_enrol AS e1
 WHERE e1.roleid = 5
 GROUP BY e1.courseid) AS studentCountCourse ON studentCountCourse.courseid = c.id
INNER JOIN (
 SELECT 
  t1.id
  ,count(t1.id) AS numStudents
 FROM prefix_user AS t1
 INNER JOIN prefix_user_enrolments AS ue ON ue.userid = t1.id
 INNER JOIN prefix_enrol AS e ON e.id = ue.enrolid
 INNER JOIN prefix_enrol AS e2 ON e2.courseid = e.courseid AND e2.roleid = 5
 INNER JOIN prefix_course AS c ON c.id = e.courseid
 WHERE e.roleid = 3
 GROUP BY t1.id) AS studentCountTeach ON studentCountTeach.id = t.id
ORDER BY e.courseid;

If I am misunderstanding your schema please let me know by explaining what each of the tables is used for.

Upvotes: 1

Related Questions