CODEWITHSUNDEEP
sqlpostgresqlgroup-byaggregate
Gnudiff
Gnudiff

Reputation: 4305

Postgres query to get last date value for period along with sum for period?

I can't seem to understand how to do a simple thing in a single query.

I have a table containing balance of items present in system at end of every day:

CREATE TEMPORARY TABLE t1 (
    item character varying(10),
    storage_date date,
    qty integer
);

COPY t1 (item, storage_date, qty) FROM stdin;
A       2021-01-01      10
A       2021-01-02      10
A       2021-01-03      10
A       2021-01-04      5
A       2021-01-05      3
B       2021-01-01      1
B       2021-01-02      2
B       2021-01-03      100
B       2021-01-04      50
\.

I need to get average number of items stored per period along with the balance of each item at the end of period.

Let's say the period is between 2021-01-01 and 2021-01-05 .

Now, of course, average is simply:

SELECT item, sum(qty)/5 FROM t1 GROUP BY item ORDER BY item;
 item | avg
------+-----
 A    |   7
 B    |  30
(2 rows)

But I need it to also include the balance (qty) for each item for the last date of the period = 2021-01-05 (As you can see, item B also has no entry for that date, meaning the stock was 0 at that point. But it is OK, if it is either NULL or 0). Wanted:

 item | avg | balance at end of period
------+-----+---------
 A    |   7 |   3
 B    |  30 |  NULL
(2 rows)

I tried to use last_value() aggregate function:

SELECT item,last_value(qty) OVER (ORDER BY storage_date),sum(qty)/5 from t1 group by 1,2;

But that obviously doesn't work (I am not fluent at more advanced SQL; JOINS, GROUP BY and HAVING is what I understand).

What am I missing? Surely there must be some simple way rather than writing two queries?

Upvotes: 1

Views: 847

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use a subquery:

SELECT item, sum(qty)/5, balance
FROM (SELECT t1.*,
             FIRST_VALUE(qty) OVER (PARTITION BY item ORDER BY storage_date DESCk) as balance
      FROM t1
     ) t1
GROUP BY item, balance
ORDER BY item;

You can do this without a subquery as well:

I'm not sure why you are using SUM(qty) / 5 instead of AVG(qty), but that logic is from your query.

SELECT item, sum(qty)/5,
       (ARRAY_AGG(qty ORDER BY storage_date DESC))[1] as balance
FROM t1
GROUP BY item
ORDER BY item;

Upvotes: 2

Serg
Serg

Reputation: 22811

You can use LEFT JOIN

SELECT ta.*, tb.qty
FROM(
   SELECT item, sum(qty)/5 
   FROM t1 
   GROUP BY item) ta
LEFT JOIN  t1 tb ON ta.item = tb.item AND tb.storage_date = TO_DATE('20210105','YYYYMMDD')
ORDER BY item;

Upvotes: 1

Related Questions