How to sum a column in Postgres

I create this table and inserted in it. I use PostgreSQL. How to write a query to have what i need. I need total amount of each column. below it Thanks.

CREATE TABLE testTable (
    id INT,
    service TEXT,
    is_checked BOOLEAN
);

insert into testTable (id, service, is_checked)
values (1,'service1',TRUE),
 (2,'service2',TRUE),
 (3,'service3',TRUE),
 (4,'service2',TRUE),
 (5,'service3',TRUE),
 (6,'service1',FALSE),
 (7,'service1',FALSE),
 (8,'service1',FALSE),
 (9,'service1',TRUE);

SELECT service as name,
   Sum(case when is_checked=true then 1 else 0 end) As Succeed,
   Sum(case when is_checked=false then 1 else 0 end) As Failure
from testTable group by service

The below query result in the table “What I have”

enter image description here

What I need is the table “What I want”

enter image description here

Answer

You can use grouping sets for the additional summary.

select service,
       count(*) filter (where is_checked),
       count(*) filter (where not is_checked)
from t
group by grouping sets ( (service), () )

Advertisement

Answer