'select count of client activity with titles

I have a clients table.

Clients

CREATE TABLE IF NOT EXISTS public.clients
(
    id integer NOT NULL DEFAULT nextval('clients_id_seq'::regclass),
    "isActive" boolean NOT NULL,
    CONSTRAINT clients_pkey PRIMARY KEY (id)
)

im looking for an output similar to this:

+----------+-------+
| name     | count |
+----------+-------+
| Active   | 9     |
+----------+-------+
| inActive | 4     |
+----------+-------+

Basically an output that displays a count of clients by activity boolean, and also include static titles (Active, inActive) so i can call on this request and display it on a chart.

Please help me if you have any ideas

sqlpostgresql


Solution 1:[1]

Try

SELECT 
    'Active' as name, count(id) as count
FROM TABLE
WHERE isActive is true
UNION ALL
SELECT 
    'inActive' as name, count(id) as count
FROM TABLE
WHERE isActive is false

Solution 2:[2]

You can "translate" the boolean flag using a CASE expression, then group by that:

select case 
         when "isActive" then 'Active' 
         else 'inActive' 
       end as name,
       count(*)
from clients
group by name;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JohanB
Solution 2 a_horse_with_no_name

Related Questions