I have two tables (timesheet and tasks) each contains an hour value column "allocated hours" and "actual hours" of which I am trying to get the sum of both of these values.
also the timesheet table contains a integer value for "staff_id" which corresponds to the "assigned_to" in the task table
the task table contains:
task_id INT(11)
assigned_to INT(11)
date_start DATE
hrs DECIMAL (10,0)
the timesheet table contains:
timesheet_id (int)
name varchar(100)
hours decimal(10,0)
staff_id(INT 11)
my query looks like:
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 will (incorrectly) 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
The above is what I am trying to get, However
all of the results are correct but John Smith's assigned hours get doubled.
I know it has to do with the "Grouping Pitfall"
as described here:
but I just go cross eyed trying to figure this out.
can someone point me in the right direction?
(edit again)
If I run a query just on the task table:
SELECT
task.assigned_to,
SUM(task.hrs) AS allocated_hrs
FROM task
GROUP BY task.assigned_to
It (correctly) results in:
assigned_to | allocated_hrs |
----------------------------
4 | 682
7 | 378
2 | 40
6 | 227
9 | 262
you can see that the user ID of "4" which is John Smith has doubled (and also ID 6)
running a query on just the timesheet table :
SELECT
timesheet.name,
SUM(timesheet.hours) AS actual_hours
FROM timesheet
GROUP BY timesheet.name
correctly results in :
name | Actual_hrs
-------------------------
Jane Doe | 19.5
John Smith | 6.5
Test User1 | 4
Test User2 | 5
running the query supplied by JoachimL results in :
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
解决方案
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;
165
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
