Avijit Avijit - 1 month ago 17
SQL Question

Grouping with HAVING condition

I am having a table of following fields.

enter image description here

I would like to get output like Status in only Invalid_vod within Account_Id group

Account_Id Address_Id Status
AC001 ADD12345 Invalid_vod
AC003 ADD12348 Invalid_vod
AC003 ADD12349 Invalid_vod


I was doing like this but unable to get my intended result.

select [Account_Id],[Address_Id],[Status]
from [DBFile]
group by [Account_Id],[Address_Id],[Status]
having [Status] = 'Invalid_vod'

Answer

You can use a windowed version of COUNT for this:

;with cte as (
   select [Account_Id], [Address_Id], [Status],
          count(case when [Status] <> 'Invalid_vod' then 1 end)
          over (partition by [Account_Id]) AS cnt
   from [DBFile]
)
select [Account_Id], [Address_Id], [Status]
from cte
where cnt = 0