CODEWITHSUNDEEP
sqlpostgresqlaggregate
lusketeer
lusketeer

Reputation: 1930

Conditionally grouping by date

I'm having a bit trouble figure this one out.

I have two tables items and stocks

items
id | name 
1  | item_1    
2  | item_2    

stocks
id | item_id | quantity | expired_on
1  |    1    |    5     |  2015-11-12
2  |    1    |    5     |  2015-11-13
3  |    2    |    5     |  2015-11-12
4  |    2    |    5     |  2015-11-14

I want to be able to retrieve a big table grouped by date, and for each date, group by item_id and show the sum of the quantity that's not expired.

result
date        | item_id | unexpired 
2015-11-11  |    1    |    10     
2015-11-11  |    2    |    10     
2015-11-12  |    1    |    5     
2015-11-12  |    2    |    5     
2015-11-13  |    1    |    0     
2015-11-13  |    2    |    5     
2015-11-14  |    1    |    0     
2015-11-14  |    2    |    0

I'm able to retrieve the result if it's just one day

SELECT 
  items.id, SUM(stocks.quantity) as unexpired
FROM 
  items LEFT OUTER JOIN stocks 
  ON items.id = stocks.item_id
WHERE 
  stocks.expired_on > '2015-11-11'
GROUP BY
  items.id, stocks.quantity

I searched around, found something called DatePart, but it doesn't seem like what I need.

Upvotes: 4

Views: 105

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Using the convenient cast from boolean to integer, which yields 0, 1 or null, to sum the unexpired only

select
    to_char(d, 'YYYY-MM-DD') as date,
    item_id, 
    sum(quantity * (expired_on > d)::int) as unexpired
from
    stocks
    cross join 
    generate_series(
        '2015-11-11'::date, '2015-11-14', '1 day'
    ) d(d)
group by 1, 2
order by 1, 2
;
    date    | item_id | unexpired 
------------+---------+-----------
 2015-11-11 |       1 |        10
 2015-11-11 |       2 |        10
 2015-11-12 |       1 |         5
 2015-11-12 |       2 |         5
 2015-11-13 |       1 |         0
 2015-11-13 |       2 |         5
 2015-11-14 |       1 |         0
 2015-11-14 |       2 |         0

The cross join to the generate_series supplies all dates in the given range.

The data used above:

create table stocks (
    id int,
    item_id int,
    quantity int,
    expired_on date
);
insert into stocks (id,item_id,quantity,expired_on) values
(1,1,5,'2015-11-12'),
(2,1,5,'2015-11-13'),
(3,2,5,'2015-11-12'),
(4,2,5,'2015-11-14');

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You need to generate the list of dates and then use cross join to get the full combinations of dates and items. Then, a left join to the stock table gives the expired on each date. A cumulative sum -- in reverse -- calculated unexpired:

select d.dte, i.item_id,
       sum(quantity) over (partition by i.item_id
                           order by d.dte desc
                           rows between unbounded preceding and 1 preceding
                          ) as unexpired
from (select generate_series(min(expired_on) - interval '1 day', max(expired_on), interval '1 day') as dte
       from stocks
      ) d cross join
      items i left join
      stocks s
      on d.dte = s.expired_on and i.item_id = s.item_id;

Upvotes: 1

Related Questions