CODEWITHSUNDEEP
mysql
Sasha
Sasha

Reputation: 8705

MySQL - count active/inactive/banned users and sellect users based on status in one query

I have users table, and user can have one of the three statuses:

active (0)
inactive (1)
banned (2)

Is it possible to count all type of users separately and take only users for selected status in one query. For example I want to show information how many user are active / inactive / banned and show all active users?

The table structure is simple:

id | firstName | lastName | email | password | status

I'm trying to achieve something like this:

Active Users (10) | Inactive Users (2) | Banned Users (7)

List of all active users:

1) John Doe
2) John Doe
3) John Doe
4) ...

Upvotes: 1

Views: 5027

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

There is no way to get your expected output with one single query (since the strcuture of the result is different). You have to use two different querys to get the expected information of the users table:

1 - You can get the status overview with the following:

SELECT 
  SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS active_users,
  SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS inactive_users,
  SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS banned_users
FROM users

2 - To get all users with status = 0 (active) you can use the following:

SELECT *
FROM users
WHERE status = 0

demo on dbfiddle.uk

Upvotes: 7

Bilal Siddiqui
Bilal Siddiqui

Reputation: 3629

A short version of Sebastian Brosch's answer:

SELECT 
    SUM(status = 0) AS active_users, 
    SUM(status = 1) AS inactive_users, 
    SUM(status = 2) AS banned_users 
FROM users;

Upvotes: 2

Related Questions