Select CASE WHEN ALIAS with COUNT not returning ZERO Values

Question

I'm trying to count a number of users based on their assigned files, and grouped by those files' statuses with the following MySQL query:

SELECT
  (CASE WHEN F.status IS null THEN 'records' 
   WHEN F.status = 0 THEN 'prospects' 
   WHEN F.status >= 1 AND F.status < 4 THEN 'open'
   WHEN F.status = 4 THEN 'archived' END) AS FileStatus,
  count(DISTINCT U.id) as users
FROM
  users_table U
  LEFT OUTER JOIN files_table as F on (F.user_id = U.id or F.user_id IS NULL)
WHERE U.team = 1  
GROUP BY FileStatus DESC

My problem is that is if no users have a file with a certain status, the query wont return that row:

For the moment the query returns values like so:

+------------+-------+
| FileStatus | users |
+------------+-------+
| prospects  |   5   |
| open       |   10  |
| archived   |   12  |
+------------+-------+

But I'd like to have zero or null values appear as well like this:

+------------+-------+
| FileStatus | users |
+------------+-------+
| records    |   0   |
| prospects  |   5   |
| open       |   10  |
| archived   |   12  |
+------------+-------+

I'm sure this is super simple but any help would be most appreciated ✌️

Answer 1

To get all file statuses, you would need to have a table with these statuses. If not available, you can produce it inline with a subquery.

Secondly, the condition on the user's team should be part of the join condition, otherwise (when you put it in the where clause) it will turn the outer join into an inner join.

After comments about the "records" count, it anyway made no sense anymore to use an outer join. I would suggest a union for that category instead:

SELECT     FileStatus,
           count(DISTINCT U.id) as users
FROM       (SELECT 0 as status, 'prospects' as FileStatus
            UNION ALL SELECT 1, 'open'
            UNION ALL SELECT 2, 'open'
            UNION ALL SELECT 3, 'open'
            UNION ALL SELECT 4, 'archived') as C
INNER JOIN files_table as F 
        on F.status = C.status
INNER JOIN users_table U
        on F.user_id = U.id
       and U.team = 1  
GROUP BY   FileStatus DESC
UNION ALL
SELECT    'records',
          count(*)
FROM      users_table
WHERE     id NOT IN (SELECT user_id FROM files_table)
AND       team = 1

If you want to work with ranges, then return a low/high end in the subquery, and adapt the join condition accordingly:

SELECT     FileStatus,
           count(DISTINCT U.id) as users
FROM       (SELECT 0 as statusFrom, 0 statusTo, 'prospects' as FileStatus
            UNION ALL SELECT 1, 3, 'open'
            UNION ALL SELECT 4, 4, 'archived') as C
INNER JOIN files_table as F 
        on F.status between C.statusFrom and C.statusTo
INNER JOIN users_table U
        on F.user_id = U.id
       and U.team = 1  
GROUP BY   FileStatus DESC
UNION ALL
SELECT    'records',
          count(*)
FROM      users_table
WHERE     id NOT IN (SELECT user_id FROM files_table)
AND       team = 1