how do I write a SQL CASE/condition to flag if all of one field is a certain value?-CodePudding

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

Account	ID	Open or Close
123456	a678	Close
123456	b192	Close
123456	j291	Close
543210	l103	Open
543210	m129	Close

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

Account	All Closed?
123456	Yes
543210	No

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.