CODEWITHSUNDEEP
mysqlsqldatabase
Prioranjan Chowdhury
Prioranjan Chowdhury

Reputation: 23

Mysql Query is not working and shows an error

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

Answers (3)

Gordon Linoff
Gordon Linoff

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

Daniel E.
Daniel E.

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

Ravi Chauhan
Ravi Chauhan

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

Related Questions