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@example.com 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 firstname.lastname@example.org has count as 1
similarly email@example.com has 2 active apps of different app_id.
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]