I have a above table, and expected output would be

Let me explain we are displaying each user has how many enabled app which are active state.

So lets take the first email id [email protected] which returns 1 because this user has 2 app_id associated with it. 2553 and 2554. now in 2553 we wont consider because although it has active it also has an inactive value in it. But for app_id 2554 we have an active enabled_app but there is no inactive value for it. Hence [email protected] has count as 1

similarly [email protected] has 2 active apps of different app_id.

CodePudding user response：

SELECT
    [user], COUNT([app_id])
FROM
    YourTable
WHERE
    enabled_app = 'active'
    AND [app_id] NOT IN (SELECT [app_id] FROM YourTable WHERE enabled_app = 'inactive')
GROUP BY
    [user]

CodePudding user response：

I would use a NOT EXISTS condition:

select t1."user", 
       count(*)
from the_table t1
where enabled_app = 'active'
  and not exists (select * 
                  from the_table t2
                  where t2."user" = t1."user"
                    and t2.app_id = t1.app_id
                    and t2.enabled_app = 'inactive')
group by t1."user"
order by t1."user"

CodePudding user response：

Using EXCEPT (aka minus) you get a raw data of the active applications that are not in inactive state

select "user", app_id from tab where enabled_app = 'active'
except 
select "user", app_id from tab where enabled_app = 'inactive'

As a positive effect the set operation removes duplicates, so you need not to thing about if count (distinct ... should be used.

The next step is a simple aggregation

with tab2 as (
select "user", app_id from tab where enabled_app = 'active'
except 
select "user", app_id from tab where enabled_app = 'inactive'
)
select "user", count(*)
from tab2
group by 1