How can I have an aggregate function in the WHERE clause? Using PostgreSQL 9.3.24

Issue

Just simple operation, but I don't know how to do that best simplest way.

How can I display results only when sum of available_counts for same product_id is 0 and onway is 0 or NULL? I.e. 130479 in this case. I want display all values, for every store_id.

Thank you.

Update

I tried aggregate functions are not allowed in WHERE.

select
*

from
j_product_store_availability psa 

where
sum(available_count) = '0'
and onway = '0'

group by
product_id

order by
product_id


Input.

product_id | store_id | available count | onway 
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0
3          | 1        | 0               | 0
3          | 2        | 0               | 0
3          | 3        | 1               | 0
4          | 1        | 0               | 0
4          | 2        | 0               | 0
4          | 3        | 0               | 1


Expected output.

product_id | store_id | available count | onway 
1          | 1        | 0               | 0
1          | 2        | 0               | 0
1          | 3        | 0               | 0
2          | 1        | 0               | 0
2          | 2        | 0               | 0
2          | 3        | 0               | 0
You have to use a window function, which does something similar to a GROUP BY, but outpots all rows and not just the groups.

SELECT *
FROM (SELECT *,
             sum(available_count) OVER (PARTITION BY product_id) AS sum_count
      FROM j_product_store_availability psa) AS subq
WHERE onway = 0
  AND sum_count = 0
ORDER BY product_id;