Aggregating sum day by day from multiple joins returns unexpected results

Question

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

Answer 1

I first thought that you needed to GROUP BY the datetable in a derived table as well, to avoid a cross join but it seems that the datetable already has distinct dates so that is not the cause. The problem is the <= in the (more than one) joins. This creates a kind of cross join and wrong results.

So, the solution is to do the LEFT JOIN and the <= inside the derived tables and in the external level join with equality = and no GROUP BY:

Another issue comes from not using GROUP BY DATE(DateColumn). Use that - and then the ON conditions can be simplified as well.

I also changed the DATE(tx.created) <= DATE(dt.thedate) condition to tx.created < (dt.thedate + INTERVAL 1 DAY) so an index on (created, hours) can be used:

SELECT
DATE_FORMAT(dt.thedate, '%Y-%m-%d') AS the_date,
COALESCE(tt.sum_task, 0) AS sum_t,
COALESCE(tg.sum_group, 0) AS sum_g

FROM datetable AS dt

LEFT JOIN   (                                  -- this could be an INNER JOIN, no difference
                SELECT
                dt.thedate,
                SUM(tx.hours) as sum_task

                FROM datetable AS dt
                  LEFT JOIN tasks AS tx
                    ON tx.created < (dt.thedate + INTERVAL 1 DAY)

                 -- Some extra where clauses here   -- move them to the ON above

                GROUP BY dt.thedate
            )
            AS tt ON tt.thedate = dt.thedate

LEFT JOIN   (                                 -- this could be an INNER JOIN, no difference
                SELECT
                dt.thedate,
                SUM(gx.hours) as sum_group

                FROM datetable AS dt
                  LEFT JOIN groups AS gx
                    ON gx.created < (dt.thedate + INTERVAL 1 DAY)

                 -- Some extra where clauses here   -- move them to the ON above

                GROUP BY dt.thedate
            )
            AS tg ON tg.thedate = dt.thedate

ORDER BY dt.thedate ;

Tested at SQLFiddle

Answer 2

In 2nd left join you have to make one more combination with "...,tx.groupid as gid FROM tasks tx GROUP BY tx.created,tx.groupid" Then you can get the proper result.

SELECT Convert(date,dt.thedate) as Thedate,isnull(SUM(tt.sum_task),0) as sum_t,tt.gid
FROM datetable dt
JOIN   

(SELECT Convert(date,tx.created) as Created_Date,Isnull(SUM(tx.hours),0) as sum_task,tx.groupid as gid FROM tasks tx GROUP BY tx.created,tx.groupid )

AS tt ON Convert(date,tt.Created_Date) <= Convert(date,dt.thedate)
--left outer Join 
--( SELECT Convert(date,gx.created) as created_date2,SUM(gx.hours) as sum_group FROM groups gx GROUP BY gx.created)
--AS tg ON convert(DATE,tg.created_date2) <= Convert(date,tt.Created_Date)

GROUP BY dt.thedate,tt.gid
ORDER BY dt.thedate ASC

for. Ex, i have got like this:

date     sum_t grpid
2014-12-03  1   1
2014-12-04  3   1
2014-12-05  3   1
2014-12-05  5   2
2014-12-06  3   1
2014-12-06  10  2
2014-12-07  3   1
2014-12-07  10  2
2014-12-08  3   1