Need to have subquery within subquery

Question

I have a stock table which holds for example

 Partnumber | Depot | flag_redundant
------------+-------+----------------
          1 |     1 |              5
          1 |     2 |              0
          1 |     3 |              0              
          1 |     4 |              5
          2 |     1 |              0
          2 |     2 |              0
          2 |     3 |              0
          2 |     4 |              0

I need to be able to see the depots in which the parts have not been flagged as redundant, but the flag_redundant has been at least been flagged once for that part, and I need to ignore any parts where there has not been a flag flagged.

Any help appreciated!

I'm thinking of something along the lines of ....

SELECT stock.part, stock.depot, 
       OrderCount = (SELECT CASE WHEN Stock.flag_redundant = 5 THEN 1 end as Countcolumn FROM stock C)
  FROM stock 

 Partnumber | MissingDepots
------------+---------------
          1 | Yes

Answer 1

You can group by partnumber and set the conditions in the HAVING clause:

select 
  partnumber, 'Yes' MissingDepots 
from stock 
group by partnumber
having 
  sum(flag_redundant) > 0 and 
  sum(case when flag_redundant = 0 then 1 end) > 0 

Or:

select 
  partnumber, 'Yes' MissingDepots 
from stock 
group by partnumber
having sum(case when flag_redundant = 0 then 1 end) between 1 and count(*) - 1 

See the demo.
Results:

> partnumber | missingdepots
> ---------: | :------------
>          1 | Yes    

Answer 2

Assuming you want to get these partnumbers that contain data sets with flag_redundant = 5 AND 0:

demo:db<>fiddle

 SELECT 
    partnumber,
    'Yes' AS missing
FROM (
    SELECT
        partnumber,
        COUNT(flag_redundant) FILTER (WHERE flag_redundant = 5) AS cnt_redundant,   -- 2
        COUNT(*) AS cnt                                                             -- 3
    FROM
       stock
    GROUP BY partnumber                                                             -- 1
) s
WHERE cnt_redundant > 0                                                             -- 4
   AND cnt_redundant < cnt                                                          -- 5

1. Group by partnumber
2. Count all records with flag_redundant = 5
3. Count all records
4. Find all partnumbers that contain any element with 5 ...
5. ... and which have more records than 5-element records