MySQL count group_concat using case

January 15, 2023

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;