MySQL query to count attendance

Question

So this question might look really impractical, but it's an in class assignment and I'm really not sure how to approach it. Also I'm new to SQL in general, so bear with me.

Given a MySQL table attendance that marks student attendance with the following schema:

• student_id: ID number of student that attended a lecture
• lecture: name of the lecture

Each student can attend more than 1 lecture, and each student can attend the same lecture multiple times. Write a query to return all student IDs in the table, which lecture they attended and how many times they attended.

Example: input table attendance

student_id	lecture
1	math
2	math
3	math
1	math
3	math
2	math
3	math
3	physics
4	physics
2	physics
4	physics

The query should return:

student_id	lecture	attendance
1	math	2
2	math	2
3	math	3
2	physics	1
3	physics	1
4	physics	2

I created a table like the example given at https://paiza.io/projects/_VVe5NKIcSNzRGura4aA3g?language=mysql to try.

My first thought is

SELECT student_id,
       lecture,
       count(*)
FROM attendance
GROUP BY lecture;

But that would return Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.attendance.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. Not sure how to approach this question. Thanks in advance.

Edit: well, guess I solved it myself: select student_id, lecture, count(*) from attendance group by student_id, lecture;. Accuracy check and input appreciated, thanks

Answer 1

You should aggregate by student_id and lecture:

SELECT student_id, lecture, COUNT(*) AS cnt
FROM attendance
GROUP BY student_id, lecture;

The error message you are currently seeing stems from aggregating by lecture alone but also try to select student_id. It is not clear which value of student_id MySQL should return for each lecture group of records.

Answer 2

Your query should look like:

SELECT student_id,
       lecture,
       count(*) as attendance
FROM attendance
GROUP BY lecture, student_id;

In reference to the error you got, check setting sql_mode