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
SQL Executed:
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;
Expected Results:
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.
>Solution :
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;