I have students work system. Each student can submit multiple assignments, and the assignments are either written or practical. For each student, I need to know who has ONLY submitted practical work, no written work.
I have two tables in use: assignments and workTypes.
assignments looks something like this (excuse date format):
userid workID submissionDate -------------------------------- 1 254 1/1/2022 1 723 0000-00-00 1 435 3/1/2022 1 845 4/2/2022 2 254 6/2/2022 2 435 2/6/2022 2 923 0000-00-00 2 845 6/2/2022
WorkTypes looks something like this:
workID type -------------------- 254 written 435 practical 845 written 723 written 923 practical
SELECT assignments.userid, GROUP_CONCAT(case when workTypes.type = 'written' AND assignments.submissionDate IS NOT NULL AND assignments.submissionDate != '0000-00-00' then assignments.submissionDate end) AS 'writtenDates', COUNT('writtenDates') AS numWritten, GROUP_CONCAT(case when workTypes.type = 'practical' AND assignments.submissionDate IS NOT NULL AND assignments.submissionDate != '0000-00-00' then assignments.submissionDate end) AS 'practicalDates', COUNT('practicalDates') AS numPractical FROM `assignments` LEFT JOIN workTypes ON assignments.workID = workTypes.workID GROUP BY assignments.userid;
userID writtenDates numWritten practicalDates numPractical 1 1/1/2022,4/2/2023 2 3/1/2022 1 2 6/2/2022,6/2/2022 2 2/6/2022 1
Once I have this data working, I want to add a clause to only select those results that have no written work but some practical, so something like:
HAVING numPractical > 0 AND numWritten < 1
At the moment, my query is returning the same number in the numWritten and numPractical fields for every student (4000+ students) which cannot be correct and also, the num fields are showing much higher numbers than there are dates, so for example I can see one row that has three dates in, but the numPractical field says 23. I am assuming 23 is the count of the number of rows of work for each student or something instead of the number of dates in the group_concat field, but I can’t work out why.
Use conditional aggregation with a boolean expression:
SELECT a.userid, GROUP_CONCAT(CASE WHEN wt.type = 'written' AND a.submissionDate IS NOT NULL AND a.submissionDate != '0000-00-00' THEN a.submissionDate END) AS writtenDates, SUM(wt.type = 'written') AS numWritten, GROUP_CONCAT(CASE WHEN wt.type = 'practical' AND a.submissionDate IS NOT NULL AND a.submissionDate != '0000-00-00' THEN a.submissionDate end) AS practicalDates, SUM(wt.type = 'practical') AS numPractical FROM assignments a LEFT JOIN workTypes wt ON a.workID = wt.workID GROUP BY a.userid;