count-absent.sql

select distinct student, date, sum(if(ifnull(k1.hour, 0)>0, 1, 0)) as fornoon, 
sum(if(ifnull(k2.hour, 0)>0, 1, 0)) as afternoon  from t1 
left join t1 k1 on k1.student=t1.student, k1.date=t1.date and hour between 1, 4
left join t1 k2 on k2.student=t1.student, k2.date=t1.date and hour between 5, 7


; another approach, not tested

SELECT student, date 
  count(CASE WHEN t1.hour between 1,4 THEN 1 END ) AS fornoon,
  count(CASE WHEN t1.hour between 5,7 THEN 1 END ) AS afternoon
  from t1
  group by student, date
  order by student, date

thanks, let me try