Return to Question

deleted 14 characters in body

Source Link

edited Dec 3, 2018 at 11:03

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 group_concat(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.

|group_concat(|    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 ?

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 group_concat(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.

|group_concat(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 ?

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 ?

Source Link

asked Dec 3, 2018 at 10:52

j10

count for missing values in group by

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 group_concat(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.

|group_concat(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 ?

mysql group-by