Postgres: Link sequence date virtual table to real table

Question

Newbie here in PostgreSQL.

I have some SQL which basically lists a sequence of dates from 2/4/2013 to 10/7/2013. For example:

select date(generate_series(DATE '20130402', DATE '20131007', interval '1' day))

Output:

"2013-04-02"
"2013-04-03"
"2013-04-04"
"2013-04-05"
"2013-04-06"

Then I have another SQL from myorder table:

select date(date_created) as date_created1, count(id) from myorder group by date_created1 order by date_created1 desc

Output:

"2013-08-12;2"
"2013-08-08";1"
"2013-08-02";1"
"2013-08-01";1"

Basically, this shows the total orders per day.

My question is how do I link the first SQL to the 2nd one, so that it will output date and count sequentially (ordered by date). Also, if no order is found in the "myorder" table, it will show "0" instead.

For Example:

2013-08-11 does not have any order record, so the count column will show "0".

More less like this:

"2013-08-12;2"
"2013-08-11;0"
"2013-08-10;0"
"2013-08-09;0"
"2013-08-08;1"
"2013-08-02;1"
"2013-08-01;1"

Thanks for your help in advance.

MatheusOl · Accepted Answer · 2013-10-08 13:59:47Z

1

Just join both, as the following:

SELECT dt.d AS date_created1, count(m.id)
FROM
    (
    SELECT date(d)
        FROM generate_series(
            DATE '20130402', DATE '20131007', interval '1' day
        ) AS d
    ) AS dt
    LEFT JOIN myorder m ON m.date_created = dt.d
GROUP BY date_created1
ORDER BY date_created1 DESC

You could also use min/max to get the first and last date values.

edited Oct 8, 2013 at 13:59

answered Oct 8, 2013 at 2:31

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

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

3 Comments

user2856772 Over a year ago

Tried that, got this error: ERROR: set-valued function called in context that cannot accept a set

user2856772 Over a year ago

Figured out, by take "date" out of "generate_series", it solves the issue. The complete SQL would be: SELECT date(dt.d) AS date_created1, count(m.id) FROM generate_series(DATE '20130402', DATE '20131007', interval '1' day) AS dt(d) LEFT JOIN myorder m ON date(m.date_created) = date(dt.d) GROUP BY date_created1 ORDER BY date_created1 DESC Thank you @MatheusOl for the idea.

MatheusOl Over a year ago

@user2856772, you are right... I corrected to make it right, and used a different approach from yours (which I think it is clearer)... check that.

user330315 · Accepted Answer · 2013-10-08 15:31:02Z

1

I think using a CTE makes things a bit easier to read - but that is of course personal choice:

with all_dates as (
  select d::date as 
  from generate_series(DATE '2013-04-02', DATE '2013-10-07', interval '1' day) d
) 
select ad.d,
       count(mo.id) as order_count
from all_dates ad
  left join myorder mo on mo.date_created = ad.d
group by ad.d
order by ad.d;

answered Oct 8, 2013 at 15:31

user330315

Collectives™ on Stack Overflow

Postgres: Link sequence date virtual table to real table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related