Adding in missing dates from results in SQL

Question

I have a database that currently looks like this

Date     | valid_entry | profile
1/6/2015   1           | 1
3/6/2015   2           | 1
3/6/2015   2           | 2
5/6/2015   4           | 4

I am trying to grab the dates but i need to make a query to display also for dates that does not exist in the list, such as 2/6/2015.

This is a sample of what i need it to be:

Date     | valid_entry
1/6/2015   1
2/6/2015   0
3/6/2015   2
3/6/2015   2
4/6/2015   0
5/6/2015   4

My query:

select date, count(valid_entry)
from database
where profile = 1
group by 1;

This query will only display the dates that exist in there. Is there a way in query that I can populate the results with dates that does not exist in there?

Please refer following links Insert Missing Dates from a query How to fill missing dates by groups in a table in sql — Gehan Fernando
– Gehan Fernando, Commented Mar 16, 2016 at 6:24
Can you use a dummy table where you have all the dates, and then use union to get the -not in the actual table- dates? — Ketu
– Ketu, Commented Mar 16, 2016 at 6:25
I don't understand how you calculate the values for valid_entry in the expected output. Where does the 4 com from on the second line? Shouldn't that be 0 as there is no row for 2015-06-02? — user330315
– user330315, Commented Mar 16, 2016 at 7:00
@a_horse_with_no_name sorry, it should be 0. I updated the code as followed. — Bocky
– Bocky, Commented Mar 16, 2016 at 7:46

user330315 · Accepted Answer · 2016-03-16 08:01:24Z

3

You can generate a list of all dates that are between the start and end date from your source table using generate_series(). These dates can then be used in an outer join to sum the values for all dates.

with all_dates (date) as (
  select dt::date
  from generate_series( (select min(date) from some_table), (select max(date) from some_table), interval '1' day) as x(dt)
)
select ad.date, sum(coalesce(st.valid_entry,0))
from all_dates ad
  left join some_table st on ad.date = st.date
group by ad.date, st.profile
order by ad.date;

some_table is your table with the sample data you have provided.

Based on your sample output, you also seem to want group by date and profile, otherwise there can't be two rows with 2015-06-03. You also don't seem to want where profile = 1 because that as well wouldn't generate two rows with 2015-06-03 as shown in your sample output.

SQLFiddle example: http://sqlfiddle.com/#!15/b0b2a/2

^{Unrelated, but: I hope that the column names are only made up. date is a horrible name for a column. For one because it is also a keyword, but more importantly it does not document what this date is for. A start date? An end date? A due date? A modification date?}

answered Mar 16, 2016 at 8:01

user330315

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

1 Comment

Bocky Over a year ago

Thanks so much. This is very close to what i needed. Yeah the columns are made up. The actual database is much bigger than this but these are the 3 columns that I am focusing on.

Giorgos Betsos · Accepted Answer · 2016-03-16 07:22:48Z

0

You have to use a calendar table for this purpose. In this case you can create an in-line table with the tables required, then LEFT JOIN your table to it:

select "date", count(valid_entry)
from (
   SELECT '2015-06-01' AS d UNION ALL '2015-06-02' UNION ALL '2015-06-03' UNION ALL
          '2015-06-04' UNION ALL '2015-06-05' UNION ALL '2015-06-06') AS t
left join database AS db on t.d = db."date" and db.profile = 1
group by t.d;

Note: Predicate profile = 1 should be applied in the ON clause of the LEFT JOIN operation. If it is placed in the WHERE clause instead then LEFT JOIN essentially becomes an INNER JOIN.

edited Mar 16, 2016 at 7:22

answered Mar 16, 2016 at 6:23

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

2 Comments

Giorgos Betsos Over a year ago

@a_horse_with_no_name I thought it was a mysql question!

Bocky Over a year ago

Sadly, This is not what i am looking for as this is a little too static for what I need it for.

Collectives™ on Stack Overflow

Adding in missing dates from results in SQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related