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 ?

• j10: no. we are just using the values from the app level

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;

• nobsvalo: j10 ... i'd really like to know if this answer gives you the correct result and also if it's faster than selected one.
• Lennart - Slava Ukraini: Status with no users will not be present with your query. You need to create the domain somehow (one example is in the accepted answer).