select count of client activity with titles-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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;