Fill in missing dates for multiple values with a GROUP BY

Question

I'm trying to write a single query for retrieving time information for multiple users over a date range.

My tables are set up as follows:

Entry:

user_id  | entry_date  | entry_hours
1        | 2013-11-12  | 4
1        | 2013-11-12  | 5
1        | 2013-11-13  | 3
2        | 2013-11-13  | 7
....

Calendar:

calendar_date
.....
2013-11-10
2013-11-11
2013-11-12
2013-11-13
2013-11-14
.....

If I run this query:

SELECT *, e.user_id, e.entry_date, COALESCE(SUM(e.entry_hours), 0) as total
            FROM `entry` e
            WHERE entry_date BETWEEN '2013-11-12' and '2013-11-13'
            GROUP BY e.user_id, e.entry_date

I get something to the effect of

user_id  |  entry_date  |  total
  1      |  2013-11-12  |  9
  1      |  2013-11-13  |  3
  2      |  2013-11-13  |  7

When I would like this instead:

user_id  |  entry_date  |  total
  1      |  2013-11-12  |  9
  1      |  2013-11-13  |  3
  2      |  2013-11-12  |  0
  2      |  2013-11-13  |  7

Where it fills in the missing date with a 0.

EDIT: I would also need it to pull in user_id's with 0's when they have no listed entries. Say I included in there WHERE clause AND user_id IN (1,2,3), I would get something like:

user_id  |  entry_date  |  total
  1      |  2013-11-12  |  9
  1      |  2013-11-13  |  3
  2      |  2013-11-12  |  0
  2      |  2013-11-13  |  7
  3      |  2013-11-12  |  0
  3      |  2013-11-13  |  0

The idea behind the calendar table was to be able to pull all dates in a range and join them with the entry table to fill in those missing dates, but I've been unable to figure out how to do this.

Can anyone point me in the right direction?

Anon · Accepted Answer · 2013-11-26 20:12:07Z

1

SELECT
  u.user_id,
  c.calendar_date,
  COALESCE(SUM(e.entry_hours), 0)
FROM calendar c
CROSS JOIN users u
LEFT JOIN entry e
   ON (c.calendar_date = e.entry_date AND u.user_id = e.user_id)
GROUP BY u.user_id,c.calendar_date

edited Nov 26, 2013 at 20:12

answered Nov 26, 2013 at 19:54

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Samsquanch Over a year ago

I knew I was forgetting something in the original post -- see my edit. I also need it to fill in with 0's where the user_id is supplied in the where clause, but there are no entries in the table for that user.

Anon Over a year ago

You'll need a separate table that lists your users, just like the calendar

Samsquanch Over a year ago

I do have a user table that contains the user_id -- would I need to join on that as well?

Anon Over a year ago

Yes. Everything in your result has to come from somewhere. If you want user_ids that are missing from the entries table, you need to bring in a table that has them.

Collectives™ on Stack Overflow

Fill in missing dates for multiple values with a GROUP BY

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related