How to write SQLite code to show a specified code along with its associated codes from group_concat function?

mq19745422 0
Viewed 52

I am trying to create a table where the code N09 is included, where a student was assigned a set of codes that contains N09, and "Status Complete" was yes. I wanted to use group_concat to see if each set contains N09. I saw a similar question to this but unfortunately, it did not satisfy my goal for Table 2 as it led to a problem. This problem I am experiencing is that it keeps showing 1 instead of 2, 3 for count. It also keeps showing N09, instead of N09 and its other codes from the set from the group_concat function. Is there a code to achieve my goal for Table 2 in SQLite? If my question is not clear, feel free to comment as I am new here.

Goal for Table 2:

Student ID Status Complete Status Date Status Time Code Count Group_Concat(Code)
1 yes 03/03/2021 00:00:00 N09 1 N09
2 yes 03/04/2021 10:03:10 N09 2 N09, M33
3 yes 03/04/2021 01:00:10 N09 3 N09, Y03, B55

Problem:

Student ID Status Complete Status Date Status Time Code Count Group_Concat(Code)
1 yes 03/03/2021 00:00:00 N09 1 N09
2 yes 03/04/2021 10:03:10 N09 1 N09
3 yes 03/04/2021 01:00:10 N09 1 N09

Sample Data:

Student ID Status Complete Status Date Status Time Code
1 yes 03/03/2021 00:00:00 N09
2 yes 03/04/2021 10:03:10 N09
2 yes 03/04/2021 10:03:10 M33
3 yes 03/04/2021 01:00:10 N09
3 yes 03/04/2021 01:00:10 Y03
3 yes 03/04/2021 01:00:10 B55

Code:

CREATE TABLE table2 AS
select   Student_ID
        ,Status_Complete  
        ,Status_Date
        ,Status_TIME
        ,Code
        ,count(Code) /*over (partition by Student_ID,Code)*/ as 'Count'
        ,GROUP_CONCAT(Code)
from table1
where Code in ('N09') AND Status_Complete = 'yes'
group by Student_ID, Status_Date, Status_TIME, 'Count'
HAVING 'Count'> 0 
ORDER BY Student_ID;
group-concat having group-by sqlite sql
2 Answers

the WHERE cluase you have excludes all columns that are not N09 and have the status completed, so switch zu a EXISTS clause

As Lennart points out, here the having is redundant, as all rows now will have at least the count of 1

CREATE TABLE table2 AS
select   Student_ID
        ,Status_Complete  
        ,Status_Date
        ,Status_TIME
        ,Code
        ,count(Code) /*over (partition by Student_ID,Code)*/ as 'Count'
        ,GROUP_CONCAT(Code)
from table1 t1
where EXISTS( (SELECT 1 FROM table1 WHERR Code in ('N09') AND Status_Complete = 'yes' AND Student_ID = t1.Student_ID)
group by Student_ID, Status_Date, Status_TIME
ORDER BY Student_ID;
nbk0

You should group by Student_ID only since you want only 1 row for each student.
The columns Status_Date and Status_TIME of the results that you want seem to be the min values of each student (I assume that the dates have the proper format of YYYY-mm-dd which is the only valid date format for SQLite).
Also, the condition Code = 'N09' should be checked in the HAVING clause:

CREATE TABLE table2 AS
SELECT Student_ID, Status_Complete, 
       MIN(Status_Date) Status_Date, 
       TIME(MIN(Status_Date || ' ' || Status_TIME)) Status_TIME, 
       COUNT(*) count,
       GROUP_CONCAT(Code) Codes
FROM table1
WHERE Status_Complete = 'yes'
GROUP BY Student_ID
HAVING SUM(Code = 'N09') > 0
ORDER BY Student_ID;

See the demo.

Never use single quotes for column names.
'Count' is a string literal when used in code. It never refers to a column alias.

forpas0
Related