count for missing values in group by

Question

We have a user table :

  CREATE TABLE `User` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(60) NOT NULL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `status` tinyint(4) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I need to run a group by query where I need a count of users with different status values (0, 1, 2, 3)

I tried following query :

select status, count(*) 
from User
where  
status in (0, 1, 2, 3)
group by status

this gives output for the status values present in the table e.g.

|    status            | count(*) |
|        0             |  10      |
|        1             |  11      | 

but if the database does not contain let's say users with status values = 2 or 3 these records don't appear with a count 0.

How do I go about this one ?

Answer 1

SELECT status_list.status, COUNT(User.status) 
FROM ( SELECT 0 status
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) status_list
LEFT JOIN User ON User.status = status_list.status
GROUP BY status_list.status

Answer 2

Try this:

SELECT name, counter FROM (SELECT name, count(status) as counter FROM (SELECT name, status FROM user GROUP BY by name, status)) WHERE counter > 1;

First sub query should satisfy the reasoning behind multiple statuses per user the second should count the number of different statuses per user and the main query should select only those that have more than one status.

Hopefully that helps.

EDIT: I missed it... the result should be the count of users... therefore count(name)

Perhaps like this:

SELECT count(name) as number_of_users_with_different_statuses, counter 
FROM (SELECT name, count(status) as counter 
      FROM (SELECT name, status
            FROM user
            GROUP BY by name, status))
WHERE counter > 1;