你不需要这么做 GROUP BY ...,HOUR(datenew) 但只是 person http://sqlfiddle.com/#!9/b93760/1号

SELECT person,
SUM(HOUR(datenew)=9) AS `9-10`,
SUM(HOUR(datenew)=10) AS `10-11`,
SUM(HOUR(datenew)=11)  AS `11-12`,
SUM(HOUR(datenew)=12)  AS `12-13`,
COUNT(*) 
FROM mydatatable 
WHERE mydate = '2018-01-18' 
GROUP BY person

使用派生表时，应

select 
x.person,
sum(`9-10`) as `9-10`,
.
.
.
.
.
.
.
.
 from 
(
SELECT person,
IF((HOUR(datenew))= 9, COUNT(id),'') AS `9-10`,
IF((HOUR(datenew)) = 10, COUNT(id),'') AS `10-11`,
IF((HOUR(datenew)) = 11, COUNT(id),'') AS `11-12`,
IF((HOUR(datenew)) = 12, COUNT(id),'') AS `12-13`,
IF((HOUR(datenew)) = 13, COUNT(id),'') AS `13-14`,
IF((HOUR(datenew)) = 14, COUNT(id),'') AS `14-15`,
IF((HOUR(datenew)) = 15, COUNT(id),'') AS `15-16`,
IF((HOUR(datenew)) = 16, COUNT(id),'') AS `16-17`,
IF((HOUR(datenew)) = 17, COUNT(id),'') AS `17-18`,
IF((HOUR(datenew)) = 18, COUNT(id),'') AS `18-19`,
IF((HOUR(datenew)) = 19, COUNT(id),'') AS `19-20`,
IF((HOUR(datenew)) = 20, COUNT(id),'') AS `20-21`,
IF((HOUR(datenew)) = 21, COUNT(id),'') AS `21-22`,
COUNT(*) FROM mydatatable WHERE mydate = '2018-01-18' GROUP BY person,HOUR(datenew) 
) x

group by x.person

可以使用（假）聚合函数，例如：min（）将行分组到一行中

SELECT person,
  min(IF((HOUR(datenew))= 9, COUNT(id),'')) AS `9-10`,
  min(IF((HOUR(datenew)) = 10, COUNT(id),'')) AS `10-11`,
  min(IF((HOUR(datenew)) = 11, COUNT(id),'')) AS `11-12`,
  min(IF((HOUR(datenew)) = 12, COUNT(id),'')) AS `12-13`,
  min(IF((HOUR(datenew)) = 13, COUNT(id),'')) AS `13-14`,
  min(IF((HOUR(datenew)) = 14, COUNT(id),'')) AS `14-15`,
  min(IF((HOUR(datenew)) = 15, COUNT(id),'')) AS `15-16`,
  min(IF((HOUR(datenew)) = 16, COUNT(id),'')) AS `16-17`,
  min(IF((HOUR(datenew)) = 17, COUNT(id),'')) AS `17-18`,
  min(IF((HOUR(datenew)) = 18, COUNT(id),'')) AS `18-19`,
  min(IF((HOUR(datenew)) = 19, COUNT(id),'')) AS `19-20`,
  min(IF((HOUR(datenew)) = 20, COUNT(id),'')) AS `20-21`,
  min(IF((HOUR(datenew)) = 21, COUNT(id),'')) AS `21-22`,
COUNT(*) FROM mydatatable 
WHERE mydate = '2018-01-18' 
GROUP BY person