It is possible to do extra aggregation in filter clause? (PostgreSQL)

Question

I want to write something like this:

  select 
    "id", 
    "plant", 
    "product", 
    "uom", 
    count(*) as transactions_count,
    avg(("invoice_price" / "quantity")) filter (where ("date" == max(date))  as "LAST_GUIDANCE", -- I want group by date only for this
  from "transactions"
  group by 
    "id", 
    "plant", 
    "product", 
    "uom"

Where I want to count last guidance but only for rows with last date.

Problem is that I cannot add "date" to group by, because I want this grouping only for last_guidance.. It is possible to write it somehow easily in Postgres (use "over" for ex.) or I have to create too separated queries and then join them?

Thanks for answers

Answer 1

This is not allowed. The error message is pretty clear:

ERROR: aggregate functions are not allowed in FILTER LINE 1: select count(*) filter (where x = max(x))

Nor are window functions allowed.

You can use a subquery to calculate the maximum date:

select id, plan, product, uom,
       count(*) as transactions_count,
       avg(invoice_price / quantity) filter (where date = max_date)  as LAST_GUIDANCE
from (select t.*, 
             max(date) over (partition by id, plan, product, uom) as max_date
      from transactions t
     ) t
group by id, plan, product, uom;

Note that I removed the double quotes. Don't escape column names. That just makes queries harder to write and read.