1

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 ?

  • no. we are just using the values from the app level – j10 Dec 3 '18 at 11:13
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
0

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;
  • j10 ... i'd really like to know if this answer gives you the correct result and also if it's faster than selected one. – nobsvalo Dec 3 '18 at 13:53
  • 1
    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). – Lennart Dec 3 '18 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.