Question 90633 in PostgreSQL Question posted in
The official documentation can be found here.

SQL query where i aggregate count based its status – Postgresql

user enabled_app app_id
[email protected] active 2553
[email protected] inactive 2553
[email protected] waiting 2553
[email protected] active 2554
[email protected] active 2555
[email protected] waiting 2555
[email protected] active 2556

I have a above table, and expected output would be

email count
[email protected] 1
[email protected] 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 [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.

Tags:

3

Answers


  1. 0 
    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]
    Login or Signup to reply.
  2. 0

    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"
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search