PostgreSQL Filter condition by subquery output

Question

I have the query with subquery for result output as yesterday_sum column. I need to filter only rows where yesterday_sum > 1 but can't add HAVING sum(p.profit_percent) / :workDays > 1 AND yesterday_sum > 1 because yesterday_sum is not part of the GROUP BY. And I can't add a condition for positions p because yesterday_sum is not the positions column.

SELECT u.id                              AS id,
       u.nickname                        AS title,
       sum(p.profit_percent) / :workDays AS middle,
       (
           SELECT sum(ps.profit_percent)
           FROM positions ps
           WHERE ps.user_id = u.id
             AND ps.open_at BETWEEN
               :dateYesterday AND
               :dateYesterday + INTERVAL '1 day'
           GROUP BY (ps.user_id)
       )                                 AS yesterday_sum
FROM positions p
         INNER JOIN users u ON u.id = p.user_id
    AND p.profit_percent IS NOT NULL
    AND p.parent_ticket IS NULL
    AND p.close_at IS NOT NULL
    AND p.open_at BETWEEN :dateFrom AND :dateTo
GROUP BY (u.id, u.nickname)
HAVING sum(p.profit_percent) / :workDays > 1
ORDER BY middle DESC;

How can I get rid of rows with yesterday_sum column less than 1 and NULL?

Can you share a sample input table and corresponding expected output table? — lemon
– lemon, Commented Dec 10, 2022 at 11:18

The Impaler · Accepted Answer · 2022-12-10 11:48:11Z

To use the column yesterday_sum in a WHERE clause you can produce the named column by placing the query as a subquery of the main one. Then, filtering is trivial.

For example, you can do:

select *
from (
  SELECT u.id                              AS id,
       u.nickname                        AS title,
       sum(p.profit_percent) / :workDays AS middle,
       (
           SELECT sum(ps.profit_percent)
           FROM positions ps
           WHERE ps.user_id = u.id
             AND ps.open_at BETWEEN
               :dateYesterday AND
               :dateYesterday + INTERVAL '1 day'
           GROUP BY (ps.user_id)
       )                                 AS yesterday_sum
  FROM positions p
         INNER JOIN users u ON u.id = p.user_id
    AND p.profit_percent IS NOT NULL
    AND p.parent_ticket IS NULL
    AND p.close_at IS NOT NULL
    AND p.open_at BETWEEN :dateFrom AND :dateTo
  GROUP BY (u.id, u.nickname)
  HAVING sum(p.profit_percent) / :workDays > 1
) x
where yesterday_sum >= 1  -- yesterday_sum is available here
ORDER BY middle DESC;

Collectives™ on Stack Overflow

PostgreSQL Filter condition by subquery output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related