2

I'm writing a query which looks something like this:

select parent.id,
       parent.date, 
       sum(child.amount) filter (where child.is_ok) as child_sum,
       sum(sum(child.amount) filter (where child.is_ok)) over (order by parent.date)
  from parent
  left join child on parent.id = child.parent_id
 group by parent.id, parent.date
 order by parent.date desc

As you can see, I'm using a window function to get a running total over child.amount.

The question is, is there any standard or non-standard way to reference the child_sum without copying its expression inside the window function sum?

I'm using Postgres 10.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
F0RR
  • 1,590
  • 4
  • 16
  • 30

1 Answers1

1

You can use a subquery:

SELECT id, date, child_sum,
       sum(child_sum) over (order by date)
FROM (SELECT parent.id,
             parent.date, 
             sum(child.amount) FILTER (WHERE child.is_ok) AS child_sum
      FROM parent
      LEFT JOIN child ON parent.id = child.parent_id
      GROUP BY parent.id, parent.date
     ) AS subq
ORDER BY date DESC;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yeah, that is one way. The problem is I'm using ORM to do this query, so there doesn't seen to be an easy way to do a subquery here. As there doesn't seem to be a way to reference `child_sum` within the same query, I'll accept this as a right answer. – F0RR Mar 29 '19 at 10:01
  • Perhaps you can define a view for the subquery, and get your ORM to use that. – Laurenz Albe Mar 29 '19 at 10:31