Grupo mysql devolviendo resultados incorrectos

Tengo dos tablas (hoja de horas y tareas) cada una contiene una columna de valor de hora "horas asignadas" y "horas reales" de las cuales estoy tratando de get la sum de estos dos valores. también la tabla de hojas de horas contiene un valor integer para "staff_id" que corresponde al "assigned_to" en la tabla de tareas

la tabla de tareas contiene:

task_id INT(11) assigned_to INT(11) date_start DATE hrs DECIMAL (10,0) 

la tabla de horas trabajadas contiene:

 timesheet_id (int) name varchar(100) hours decimal(10,0) staff_id(INT 11) 

mi consulta se ve así:

  SELECT timesheet.staff_id, task.assigned_to, SUM(task.hrs) AS assigned_hrs, timesheet.name, SUM(timesheet.hours) AS actual_hours FROM timesheet INNER JOIN task ON timesheet.staff_id = task.assigned_to GROUP BY timesheet.name 

que (incorrectamente) dará como resultado:

 staff_id |assigned_to |assigned_hrs | name. | actual_hours | ---------------|------------|----------------|---------------|---------------| 4 |4 | 1364 | John Smith |52 2 |2 | 80 | Jane Doe |14.5 6 |6 | 454 | Test User 1 |40 9 |9 | 262 | Test User 2 |4 

Lo anterior es lo que trato de conseguir. Sin embargo, todos los resultados son correctos, pero las horas asignadas a John Smith se duplicaron. Sé que tiene que ver con "Grouping Pitfall" como se describe aquí:

http://wikido.isoftdata.com/index.php/The_GROUPing_pitfall

pero simplemente me entran los ojos cruzados tratando de resolver esto. ¿Alguien me puede apuntar en la dirección correcta?

(editar nuevamente) Si ejecuto una consulta solo en la tabla de tareas:

  SELECT task.assigned_to, SUM(task.hrs) AS allocated_hrs FROM task GROUP BY task.assigned_to 

(Correctamente) da como resultado:

 assigned_to | allocated_hrs | ---------------------------- 4 | 682 7 | 378 2 | 40 6 | 227 9 | 262 

puede ver que la identificación de usuario de "4" que es John Smith se ha duplicado (y también la ID 6)

ejecutando una consulta solo en la tabla de hojas de horas:

  SELECT timesheet.name, SUM(timesheet.hours) AS actual_hours FROM timesheet GROUP BY timesheet.name 

resultados correctos en:

  name | Actual_hrs ------------------------- Jane Doe | 19.5 John Smith | 6.5 Test User1 | 4 Test User2 | 5 

ejecutar la consulta proporcionada por JoachimL da como resultado:

  staff_id | assigned_to | assigned_hrs | name | actual_hours ---------------------------------------------------------------------- 2 2 40 Jane Doe 19.5 4 4 24 John Smith 6.5 4 4 7 John Smith 6.5 4 4 21 John Smith 6.5 4 4 210 John Smith 6.5 4 4 28 John Smith 6.5 4 4 91 John Smith 6.5 6 6 14 Test User 1 8 6 6 91 Test User 1 8 6 6 28 Test User 1 8 6 6 3 Test User 1 8 9 9 24 Test User 2 1 9 9 91 Test User 2 1 9 9 56 Test User 2 1 

Aquí hay un violín http://sqlfiddle.com/#!2/ef680

 SELECT x.* , SUM(y.hrs) n FROM ( SELECT t.staff_id , t.name , SUM(t.hours) actual_hours FROM timesheet t GROUP BY t.staff_id ) x JOIN task y ON y.assigned_to = x.staff_id GROUP BY staff_id; 

http://sqlfiddle.com/#!2/ef680/14

Sin comentarios privs …

¿Las ID 4 y 6 tienen dos filas en la parte de horas? ¿Los otros solo uno? Entonces task.hrs se duplicaría.

Algo como esto debería evitar eso. Si task_id es único, no tienes que sumr eso. (los datos de testing ayudarían)

EDITAR

 SELECT ts.staff_id, task.assigned_to, task.hrs AS assigned_hrs, ts.name, ts.actual_hours FROM task INNER JOIN (SELECT staff_id, name, SUM(hours) as actual_hours FROM timesheet GROUP BY staff_id, name) as ts ON ts.staff_id = task.assigned_to 

Lo anterior: planilla de horas del grupo por staff_id / name Luego únete a las tareas, que deberían ser solo una fila por tarea

  SELECT timesheet.staff_id, task.assigned_to, SUM(task.hrs) AS assigned_hrs, timesheet.name, SUM(timesheet.hours) AS actual_hours FROM task LEFT JOIN timesheet ON timesheet.staff_id = task.assigned_to GROUP BY timesheet.staff_id 

Prueba con IZQUIERDA UNIRSE y asegúrate de agrupar en un campo ÚNICO. "nombre" puede no ser único.

Nota: LEFT JOIN omitirá las hojas de time que no estén asignadas a una tarea. Puede invertir esto seleccionando SELECCIONAR de la hoja de time LEFT JOIN en su lugar.

Editar: vea esta respuesta: select varias sums con la consulta de MySQL y visualícelas en columnas separadas

Lo sentimos, no hay privilegios de comentario todavía.