So your table attendance stores the information which student
(identified by student_id) was in which class (identified by group_id)
at what day (identified by date) in which lesson (identified by
HOUR_X)?
ATTENDANCE TABLE
First, let's take your table schema (without the class column and only 3 periods):
CREATE TABLE IF NOT EXISTS attendance_daily (
student_id int, day date, period1 text, period2 text, period3 text);
And a bit of sample data:
INSERT INTO attendance_daily
VALUES (1, '2017-01-01', 'present', 'present', 'present'),
(2, '2017-01-01', 'present', 'absent', 'excused');
For two students, the one present all three hours, the other present the first hour, missing the second, and excused by the doctor the third.
So here is how we can make these 2 rows into 6 rows, but with common attributes (id, day, period):
SELECT t.id, t.day, t.period FROM
(SELECT student_id, day, period1 FROM attendance_daily
UNION ALL SELECT student_id, day, period2 FROM attendance_daily
UNION ALL SELECT student_id, day, period3 FROM attendance_daily
) AS t (id, day, period)
ORDER BY id, day, period;
which returns
id | day | period
----+------------+---------
1 | 2017-01-01 | present
1 | 2017-01-01 | present
1 | 2017-01-01 | present
2 | 2017-01-01 | absent
2 | 2017-01-01 | excused
2 | 2017-01-01 | present
The UNION ALL is necessary, because otherwise there would be only 1 row with id = 1, instead of 3!
And to count the presence stati (statuses):
SELECT t.id, t.day, t.presence, COUNT(*) AS count FROM
(SELECT student_id, day, period1 FROM attendance_daily
UNION ALL SELECT student_id, day, period2 FROM attendance_daily
UNION ALL SELECT student_id, day, period3 FROM attendance_daily
) AS t (id, day, presence)
GROUP BY id, day, presence ORDER BY id, day, presence ;
which returns
id | day | presence | count
----+------------+----------+-------
1 | 2017-01-01 | present | 3
2 | 2017-01-01 | absent | 1
2 | 2017-01-01 | excused | 1
2 | 2017-01-01 | present | 1
Now you can for example go and say, more than 3 times absent per day? That's a miss and getting a report or something.
You can now select on this table and fish out which student was present, absent and absent excused how many times at which day.
Now, if you want to form these rows into a string for presentation reasons, then you can follow this StackOverflow Answer for SQL-Server.
Depending on your SQL-Server version, you can also just use the string_agg function, if it's available.
DIFFERENT ATTENDANCES TABLE
If you store your attendances in a different table schema, then working with it is much simpler:
CREATE TABLE attendances (student_id int NOT NULL,
day date NOT NULL,
class int NOT NULL,
period int NOT NULL, <-- HOUR_X: hour_1 <=> period = 1
presence int NOT NULL);
-- student_id | day | classes | period AKA hour | presence_status
INSERT INTO attendances VALUES (1, '2017-01-01', 10, 1, 2),
(1, '2017-01-01', 10, 2, 1),
(1, '2017-01-01', 10, 3, 1),
(1, '2017-01-01', 10, 4, 1);
Which represents the fact, that student 1 overslept (ABSENT = 2 in the first row) the first hour of class 10 on day 2017-01-01, and was present (status = 1) for the rest of the day (which means periods 2, 3 and 4 in the last 3 rows).
And to SELECT and count (so much simpler):
SELECT student_id, day, class, presence, COUNT( presence ) AS count_presence
FROM attendances
GROUP BY student_id, day, class, presence
ORDER BY 2, 3, 1, 4, 5;
student_id | day | class | presence | count_presence
------------+------------+-------+----------+----------------
1 | 2017-01-01 | 10 | 1 | 3
1 | 2017-01-01 | 10 | 2 | 1
And to UPDATE, for example set the presence status at hour (period) = 1, for example because the student just arrived 5 minutes late (not his fault):
UPDATE attendances
SET presence = 1
WHERE student_id = 1 AND day = '2017-01-01' AND class = 10;
to get
student_id | day | class | presence | count_presence
------------+------------+-------+----------+----------------
1 | 2017-01-01 | 10 | 1 | 4
The proposed PRIMARY UNIQUE KEY for attendances (student_id, day, class, period, presence) would be (student_id, day, presence).
This allows every student to participate in as many classes and days as he wants. But for every (student, day, presence) combination, he can only be in one class with precisely one presence_status.
This way, the students can also be in different classes per day.
FOREIGN KEY REFERENCES
Here is something about FK references.
You only want to store attendances for students who actually exist.
In other words, if there is a row with student_id in your attendances_daily table, then it most certainly should exist as a student in the students table.
You can achieve this on creation of the table like so (create the students table first, otherwise there is no students_id to point to):
CREATE TABLE IF NOT EXISTS attendance_daily (
student_id int REFERENCES students (student_id), <-- FOREIGN KEY
day date, period1 text, period2 text, period3 text);
Or seperately
CREATE TABLE IF NOT EXISTS attendance_daily (
student_id int,
day date, period1 text, period2 text, period3 text,
FOREIGN KEY students_id REFERENCES students (student_id) <-- FOREIGN KEY
);
You can also go for ALTER TABLE to add a foreign key reference later on.
NULLS
In general you should strive to avoid NULLs. NULLs may have an ambiguous meaning. It can mean that data is not available at the moment, does not and will never exist, or as the number zero in a numeric attribute. With respect to your table attendances_daily, what is the meaning of a presence_status = NULL? Is it the same as absent? excused? Do you just not have the data? Then why would you store a row anyway? Because your row stores information, that can exist independently of the presence_status? Then your normalization might not be as good as it seems?
Sometimes NULLs are also okay. But think carefully about whether you want NULLs. It can make query results surprising, too.
