SQL query where i aggregate count based its status

user enabled_app app_id
h@gmail.com active 2553
h@gmail.com inactive 2553
h@gmail.com waiting 2553
h@gmail.com active 2554
b@gmail.com active 2555
b@gmail.com waiting 2555
b@gmail.com active 2556

I have a above table, and expected output would be

email count
h@gmail.com 1
b@gmail.com 2

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

So lets take the first email id h@gmail.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 h@gmail.com has count as 1

similarly b@gmail.com has 2 active apps of different app_id.

>Solution :

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

Leave a Reply