Repeating a query which returns multiple columns over a date range in postgresql

Question

If I have a table like this called orders:

id      date          value      client_name
1       01/02/2017    50         Name 1
2       02/02/2017    60         Name 2
3       02/02/2017    60         Name 2
4       03/02/2017    20         Name 2

And a query which gets me details about the orders for a given day:

Select 
  sum(value) as total_order_value,
  count(id)  as number_of_orders
from orders
where orders.date = '02/03/2017'::date

Is there a way to construct a single query which will execute that query for a range of dates, giving one row per date executed for?

I've used something like this in the past:

select date(a),
(SUB QUERY WHICH RETURNS A SINGLE VALUE)
FROM generate_series(
  (CURRENT_DATE - interval '2 months')::date,
  CURRENT_DATE,
  '1 day'
) s(a)

But this only works if the subquery returns a single value. I'm looking for a way to do this when the subquery returns multiple columns (such as in the first example above).

So from the first example above, a query which would generate the result set:

date           total_order_value      number_of_orders
01/02/2017     50                     1
02/02/2017     120                    2
03/02/2017     20                     1
04/02/2017     0                      0
05/02/2017     0                      0 
etc

Clodoaldo Neto · Accepted Answer · 2017-04-04 17:17:45Z

1

An outer join to generate_series will include the days for which there are no orders

select
    d::date,
    sum(value) as total_order_value,
    count(id)  as number_of_orders
from
    orders
    right join
    generate_series(
        (current_date - interval '2 months')::date,
        current_date,
        '1 day'
    ) gs (d) on d = orders.date
group by d

edited Apr 4, 2017 at 17:17

answered Apr 4, 2017 at 16:59

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

TalkingQuickly Over a year ago

Thanks, approach makes sense but I get a syntax error if I try and use generate series there?

Clodoaldo Neto Over a year ago

@TalkingQuickly Fixed

Collectives™ on Stack Overflow

Repeating a query which returns multiple columns over a date range in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related