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

I tried to format your question to the best of my abilities, but the description of the desired query result is still lacking. Please improve it! Try to use simple sentences, particularly if you don't write English well. — Laurenz Albe
– Laurenz Albe, Commented Jul 3, 2019 at 13:58
Can you explain why partnumber = 1 satisfies the conditions? — forpas
– forpas, Commented Jul 3, 2019 at 14:00

forpas · Accepted Answer · 2019-07-03 14:28:33Z

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

S-Man · Accepted Answer · 2019-07-03 14:14:56Z

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

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

Hi Sman the error I am getting is ERROR: syntax error at or near "(" LINE 7: COUNT(flag_redundant) FILTER (WHERE flag_redundant =... ^ SQL state: 42601 Character: 121
You really should update your database! Here is a version which should work. But now this is really close to @forpas version: dbfiddle.uk/…

Collectives™ on Stack Overflow

Need to have subquery within subquery

2 Answers 2

Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Related