PostgreSQL Filter condition by subquery output-CodePudding

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?

CodePudding user response：

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;