PostgreSQL Aggregate Function – How to Use in WHERE Clause

countpostgresqlpostgresql-9.3sum

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

Best Answer

WITH cte AS ( SELECT product_id
              FROM j_product_store_availability
              GROUP BY product_id
              HAVING 0 = SUM(available_count)
                 AND 0 = SUM(onway) )
SELECT t.*
FROM j_product_store_availability t
JOIN cte ON t.product_id = cte.product_id

Of course the solution is correct only in the case when both available_count and onway cannot be negative.

If not then use 

WITH cte AS ( SELECT product_id
              FROM j_product_store_availability
              GROUP BY product_id
              HAVING 0 = COUNT(NULLIF(available_count, 0))
                 AND 0 = COUNT(NULLIF(onway, 0)) )
SELECT t.*
FROM j_product_store_availability t
JOIN cte ON t.product_id = cte.product_id

fiddle

Related Question