Basically I have a table that has an account ID, of which there can be one or many child IDs attached. See below

I want to write a query that will basically act as a Y/N flag that tells me if all of the IDs for the account are "closed." So using the above data, id want the result to be something like

CodePudding user response：

You can combine CASE with MAX() to get the result you want:

select
  account,
  case when max(open_or_close) = 'Close' 
       then 'Yes' else 'No' 
  end as all_closed
from t
group by account

CodePudding user response：

You can count the number of Opens grouped by Account, if this count is greater than 1 so it's Open.

select account,
  case when
  count(case when OpenorClose='Open' then 1 end) = 0 then 'Close' else 'Open' end 
  as openOrClose
from TBL
group by account

See the result from here.