Mysql group, returning an invalid result

Question

I have two tables (schedule and tasks), each of which contains a column of clock values ​​“selected hours” and “actual hours”, from which I am trying to get the sum of both of these values. also the schedule table contains an integer value for "staff_id" that corresponds to "assign_to" in the task table

the task table contains:

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

schedule table contains:

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

my request looks like this:

    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

which (incorrectly) will result in:

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           

Above was what I am trying to get, but all the results are correct, but John Smith set the clock, doubling. I know this is due to the “Pitfall Grouping” as described here:

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

, . - ?

( )        :

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

() :

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

, "4", , ( ID 6)

:

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

:

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

, JoachimL, :

    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

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

Answer 1

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

Answer 2

...

ID 4 6 ? ? task.hrs .

- . task_id , .  ( )

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

: staff_id/name ,

Answer 3

    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

LEFT JOIN , UNIQUE. "" .

: LEFT JOIN , . SELECT FROM timesheet LEFT JOIN.

: . : MySQL

, .