I'm trying to make a table where the task hours and group hours are aggregated day by day. I'm able to get the wanted result for both tasks and groups, but when I try to get them in the same query, I get unexpected results.
Here's my test:
Sample data:
CREATE TABLE IF NOT EXISTS `groups` (
`id` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`hours` float NOT NULL,
`created` datetime NOT NULL
);
INSERT INTO `groups` (`id`, `name`, `hours`, `created`) VALUES
(1, 'Description of job 1', 11, '2014-12-02 10:09:52'),
(2, 'Description of job 2', 10, '2014-12-04 10:09:52'),
(3, 'Description of job 3', 25, '2014-12-11 10:09:52');
CREATE TABLE IF NOT EXISTS `tasks` (
`id` int(7) NOT NULL,
`groupid` int(11) NOT NULL,
`hours` int(5) NOT NULL,
`text` text NOT NULL,
`created` datetime NOT NULL
);
INSERT INTO `tasks` (`id`, `groupid`, `hours`, `text`, `created`) VALUES
(1, 1, 1, 'Some task on job 1', '2014-12-03 10:10:00'),
(2, 1, 2, 'Some task on job 1', '2014-12-04 10:10:00'),
(3, 1, 3, 'Some task on job 1', '2014-12-10 10:10:00'),
(4, 2, 5, 'Some task on job 2', '2014-12-05 10:10:00'),
(5, 2, 5, 'Some task on job 2', '2014-12-06 10:10:00'),
(6, 2, 1, 'Some task on job 2', '2014-12-08 10:10:00');
CREATE TABLE IF NOT EXISTS `datetable` (
`thedate` datetime NOT NULL
);
INSERT INTO `datetable` (`thedate`) VALUES
('2014-11-28 00:00:00'),
('2014-11-29 00:00:00'),
('2014-11-30 00:00:00'),
('2014-12-01 00:00:00'),
('2014-12-02 00:00:00'),
('2014-12-03 00:00:00'),
('2014-12-04 00:00:00'),
('2014-12-05 00:00:00'),
('2014-12-06 00:00:00'),
('2014-12-07 00:00:00'),
('2014-12-08 00:00:00'),
('2014-12-09 00:00:00'),
('2014-12-10 00:00:00'),
('2014-12-11 00:00:00'),
('2014-12-12 00:00:00'),
('2014-12-13 00:00:00');
Now, by running the query below, I get number of total hours per day, day by day for every task on this day and previous days.
SELECT
DATE_FORMAT(dt.thedate, '%Y-%m-%d') as the_date,
SUM(tt.sum_task) as sum_t
FROM datetable dt
LEFT JOIN (
SELECT
DATE(tx.created) as created_date,
SUM(tx.hours) as sum_task
FROM tasks tx
-- Some extra where clauses here
GROUP BY tx.created
)
AS tt ON DATE(tt.created_date) <= DATE(dt.thedate)
GROUP BY dt.thedate
ORDER BY dt.thedate ASC
Fiddle: http://sqlfiddle.com/#!2/38fe4/2/0
Good. Now I want the same type of column with the groups, so I add it the same way:
SELECT
DATE_FORMAT(dt.thedate, '%Y-%m-%d') as the_date,
SUM(tt.sum_task) as sum_t,
SUM(tg.sum_group) as sum_g
FROM datetable dt
LEFT JOIN (
SELECT
DATE(tx.created) as created_date,
SUM(tx.hours) as sum_task
FROM tasks tx
-- Some extra where clauses here
GROUP BY tx.created
)
AS tt ON DATE(tt.created_date) <= DATE(dt.thedate)
LEFT JOIN (
SELECT
DATE(gx.created) as created_date2,
SUM(gx.hours) as sum_group
FROM groups gx
-- Some extra where clauses here
GROUP BY gx.created
)
AS tg ON DATE(tg.created_date2) <= DATE(dt.thedate)
GROUP BY dt.thedate
ORDER BY dt.thedate ASC
Fiddle: http://sqlfiddle.com/#!2/38fe4/1
But now I get numbers which seem to add to itself multiple times.
When I query with only one LEFT JOIN, I get the result I want, but when I try to join them both, I get the unexpected results.
What exactly is happening here, and how can I output the group hours and task hours without the self aggregation numbers?
Expected result:
the_date sum_t sum_g
2014-11-28 NULL NULL
2014-11-29 NULL NULL
2014-11-30 NULL NULL
2014-12-01 NULL NULL
2014-12-02 NULL 11
2014-12-03 1 11
2014-12-04 3 21
2014-12-05 8 21
2014-12-06 13 21
2014-12-07 13 21
2014-12-08 14 21
2014-12-09 14 21
2014-12-10 17 21
2014-12-11 17 46
2014-12-12 17 46
2014-12-13 17 46
Actual result:
the_date sum_t sum_g
2014-11-28 NULL NULL
2014-11-29 NULL NULL
2014-11-30 NULL NULL
2014-12-01 NULL NULL
2014-12-02 NULL 11
2014-12-03 1 11
2014-12-04 6 42
2014-12-05 16 63
2014-12-06 26 84
2014-12-07 26 84
2014-12-08 28 105
2014-12-09 28 105
2014-12-10 34 126
2014-12-11 51 276
2014-12-12 51 276
2014-12-13 51 276
datetable
has any duplicate values? (eg. withSELECT DATE(thedate) FROM datetable dt GROUP BY thedate HAVING COUNT(*) > 1 ;
)