1

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
  • @LaurenzAlbe See updated question please. – genderbee Oct 18 at 9:52
1
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

  • Thank you, it works. – genderbee Oct 18 at 10:43
  • you are right, your solution works. Thanks. – genderbee Oct 18 at 11:06
1

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;
  • @LarenzAlbe Thanks, but it shows me product_id where sum of all available_count (sum of available_count of all store_id for one product_id) is not 0. – genderbee Oct 18 at 10:13
  • Condition is: display every row of available_count of every store_id (1-10) when sum of all available_counts of store_id is 0 and onway is 0. – genderbee Oct 18 at 10:20
  • Updated my question. Thank you. – genderbee Oct 18 at 10:27
  • I think I understand the problem now. Updated the answer. – Laurenz Albe Oct 18 at 10:38
  • Thank you! It is exactly what I want. – genderbee Oct 18 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.