SQL Query to count users with left join of 2 tables

Question

I am trying to count the number of users who are inactive in 1 of our 2 applications.

I have 2 tables - the Users table and the User_Applications table, which has rows containing a user id, an application id, and a boolean whether they are active or inactive.

If a user has been given access to an application, a row is added to User_Applications. So, if they've been given access to App A and App B, there are 2 rows. If they've been given access only to App A, there is only 1 row.

It is assumed that if no record exists in User_Applications regarding App B, then the user is inactive. If no records exist at all for a user in User_Applications, they are inactive in both App A and App B.

Imagine a join like this:

| user_id | app_id | active |  
|---------|--------|--------|  
| 1       | 'A'    | false  |  
| 1       | 'B'    | true   |  
| 2       | null   | null   |  
| 3       | 'B'    | true   |

If I'm counting the number of users who are inactive in A, user 1, 2, and 3 are all inactive. However, my SQL script only counts users like 1 and 2. It doesn't count users like 3. How do I include users like #3 in my query?

I wrote this SQL script to count the number of inactive users in App A:

SELECT COUNT(*) FROM Users u
LEFT JOIN User_Applications s on u.user_id = s.user_id
WHERE (s.app_id = 'A' OR s.app_id IS NULL)
AND (s.active = false OR s.active IS NULL);

Answer 1

Is the code below what you're looking for?

SELECT COUNT(*) FROM Users u
LEFT JOIN User_Applications s on u.user_id = s.user_id
WHERE (s.app_id = 'A' OR s.app_id = 'B' OR s.app_id IS NULL) AND (s.active = false OR s.active IS NULL);

Answer 2

Just use a conditional COUNT() and instead try to count how many active app has each user. Then you count who doesn't have 2 active apps

SELECT COUNT(*)
FROM (
    SELECT COUNT(CASE WHEN s.active THEN 1 ELSE NULL END) 
    FROM Users u
    LEFT JOIN User_Applications s 
      ON u.user_id = s.user_id
    GROUP BY user_id
    HAVING COUNT(CASE WHEN s.active THEN 1 END) < 2
) T

I add ELSE NULL but is the default value for ELSE, so also work if you don't add it

COUNT() doesn't count null. So if user doesn't have permission for the app, the left join return null and wont count, if user isn't active the CASE will also return null and wont count

So you will found those who doesnt have 2 active apps.

Answer 3

Count users with max 1 active App from the ('A' , 'B') list

SELECT COUNT(*) FROM Users u
LEFT JOIN ( 
   SELECT user_id, COUNT(*) nActive
   FROM User_Applications 
   WHERE app_id IN ('A' , 'B') AND active
   GROUP BY user_id 
) s on u.user_id = s.user_id
WHERE (s.nActive <= 1 OR s.nActive IS NULL)