0

To generate the list of students USN whose attendance is less than 75% along with the attendance percentage and subject code in which they have shortage, when I run the following query I am getting error. Where am I doing wrong? Can you correct it?

select attendance.usn,avg(attendance.ispresent),schedule.subcode
where avg(attendance.ispresent) in 
(select avg(attendance.ispresent) having avg(attendance.ispresent)<0.75);
peterh
  • 11,875
  • 18
  • 85
  • 108
Siraj
  • 39
  • 1
  • 5
  • Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) – e4c5 Nov 22 '16 at 11:34
  • No from clause, no group by, no sample data. – P.Salmon Nov 22 '16 at 12:18

1 Answers1

0

Neither SELECT has a FROM clause.

The inner query uses a HAVING clause but this doesn't make sense as there's no GROUP BY, so it would only produce one record.

I'm not sure what the AVG function is supposed to accomplish here anyway. What are you trying to average? Are you trying to use AVG as a tricky way to turn 0s and 1s into a percentage? If so, what's the definition of ispresent? If it's an int that is either 0 or 1, avg(ispresent) will be 0 unless all the ispresent's are 1.

You need a GROUP BY on the outer select if this is going to mean anything.

Perhaps what you want is something like:

select usn, subcode, avg(cast(ispresent as float))
from attendance
join schedule on ???
group by usn, subcode
having avg(cast(ispresent as float))<.75
Jay
  • 26,876
  • 10
  • 61
  • 112