Get Active and InActive Counts

Get Active and Inactive Counts:

In most scenarios, we need to retrieve the count of records with Active and Inactive statuses. Let's take an example: We have a table with 2 columns (name and status), where status can have values of either 0 or 1.

0 means Inactive, and 1 means Active.

To count all Active and Inactive statuses, we have two approaches: either use two different subqueries and make a final join or simplify with the query methods shown below:

---

Using the SUM function with a CASE statement:

SELECT SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS Active, SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS InActive FROM table_name;

---

Using SUM and COUNT together:

SELECT Name, SUM(status) AS [Active Status Count], COUNT(status) - SUM(status) AS [Inactive Status Count] FROM table_name GROUP BY Name;

---