Mi consulta mysql:

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) 

Mi salida de consulta actual:

enter image description here

MI SALIDA DESEADA:

enter image description here

Presentaré el informe usando PHP.

1
Sandy505 18 ene. 2018 a las 23:10

3 respuestas

La mejor respuesta

No necesita GROUP BY ...,HOUR(datenew) sino solo 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
1
Alex 18 ene. 2018 a las 21:03

Podría usar una función de agregación (falsa), por ejemplo: min () para agrupar las filas en una sola fila

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
0
scaisEdge 18 ene. 2018 a las 20:55

Usando la tabla Derivada, debería ser

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
0
maSTAShuFu 18 ene. 2018 a las 20:13
48329525