Reputation: 23
I am developing on student attendance system and try to execute this query:
select s.full_name,
s.student_id,
count(a.id) as total_present,
count(CASE WHEN TIMEDIFF(min(a.punch_in_time),'10:00:00') THEN '1' END) 'late'
from student s, attendance_record a
where a.student_id=s.student_id
and a.punch_in_date BETWEEN '2018-12-26' and '2018-12-26'
group by s.student_id
But this shows an error always "Invalid use of group function"
I can not find anything wrong . Please help me.
Upvotes: 1
Views: 69
Reputation: 1271231
I would suggest writing this as:
select s.full_name, s.student_id,
count(a.student_id) as total_present,
sum(a.punch_in_time > '10:00:00') as total_late
from student s left join
attendance_record a
on a.student_id = s.student_id and
a.punch_in_date between '2018-12-26' and '2018-12-26'
group by s.full_name, s.student_id;
Your logic for late
is rather strange. Why would someone with a timestamp of '09:59:59'
be considered late?
Upvotes: 0
Reputation: 2480
In the group by, you have to put ALL the non-aggregative columns :
select s.full_name,
s.student_id,
count(a.id) as total_present,
count(CASE WHEN TIMEDIFF(min(a.punch_in_time),'10:00:00') THEN '1' END) 'late'
from student s, attendance_record a
where a.student_id=s.student_id
and a.punch_in_date BETWEEN '2018-12-26' and '2018-12-26'
group by s.student_id , s.full_name
Note : it's better to do join table with "LEFT JOIN" or "INNER JOIN" because it's more readable
select s.full_name,
s.student_id,
count(a.id) as total_present,
count(CASE WHEN TIMEDIFF(min(a.punch_in_time),'10:00:00') THEN '1' END) 'late'
from student s
INNER JOIN attendance_record a ON a.student_id=s.student_id
where
a.punch_in_date BETWEEN '2018-12-26' and '2018-12-26'
group by s.student_id , s.full_name
Upvotes: 3
Reputation: 1477
SELECT s.full_name,
s.student_id,
Count(a.id) AS total_present,
Count(CASE
WHEN TIMEDIFF(Min(a.punch_in_time), '10:00:00') THEN '1'
END) 'late'
FROM student s,
attendance_record a
WHERE a.student_id = s.student_id
AND a.punch_in_date BETWEEN '2018-12-26' AND '2018-12-26'
GROUP BY s.full_name,s.student_id
Upvotes: 0