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 ?
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
SELECT name, counter
FROM (SELECT name, count(status) as counter
FROM (SELECT name, status
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.
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;