MySQL count group_concat using case

Question

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.

I suggest just using NULL to represent a missing date.

Tim Biegeleisen
– Tim Biegeleisen

2023-01-15 12:07:12 +00:00
Commented Jan 15, 2023 at 12:07 — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 15, 2023 at 12:07

Tim Biegeleisen · Accepted Answer · 2023-01-15 12:03:29Z

1

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,
    COUNT(CASE WHEN wt.type = 'written' AND a.submissionDate IS NOT NULL AND
                    a.submissionDate != '0000-00-00'
               THEN 1 END) 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,
    COUNT(CASE WHEN wt.type = 'practical' AND a.submissionDate IS NOT NULL AND
                           a.submissionDate != '0000-00-00'
                      THEN 1 END) AS numPractical 
FROM assignments a 
LEFT JOIN workTypes wt
    ON a.workID = wt.workID 
GROUP BY a.userid;

edited Jan 15, 2023 at 12:03

answered Jan 15, 2023 at 11:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dan Over a year ago

Can I just check that this code will count the numbers of practical and written work ONLY if the dates are present for each type?

Tim Biegeleisen Over a year ago

@Dan No...in that case you need to repeat the full CASE expressions.

Dan Over a year ago

Brilliant, thank you - also when I try to select only the records where the numPractical is more than 0 and the numWritten is less than 1 it comes up with an error. I am adding the following after the GROUP BY clause. Is it in the wrong place? WHERE numPractical > 0 AND numWritten < 1

Tim Biegeleisen Over a year ago

Use: HAVING numPractical > 0 AND numWritten < 1 ... after GROUP BY

Dan Over a year ago

Thanks for your help - do you have any recommended reading material on how to get your head around how to properly structure these types of query?

|

Collectives™ on Stack Overflow

MySQL count group_concat using case

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related