Search notes:

PostgreSQL: creating pivots with group by and filter

drop  table if exists tq84_data;
create table tq84_data (
   region  varchar(5  ) not null,
   item    varchar(3  ) not null,
   val     decimal(5,2) not null
);

insert into tq84_data values ('west' , 'foo',  10.14);
insert into tq84_data values ('west' , 'foo', 423.18);
insert into tq84_data values ('west' , 'bar',  98.49);
insert into tq84_data values ('west' , 'bar',  17.38);
insert into tq84_data values ('west' , 'bar', 204.65);
insert into tq84_data values ('west' , 'baz', 131.87);

insert into tq84_data values ('east' , 'foo',  57.24);
insert into tq84_data values ('east' , 'bar', 423.18);
insert into tq84_data values ('east' , 'bar',  75.49);
insert into tq84_data values ('east' , 'baz',  36.88);
insert into tq84_data values ('east' , 'baz',   6.64);
insert into tq84_data values ('east' , 'baz', 342.54);

insert into tq84_data values ('north', 'baz',  83.85);
insert into tq84_data values ('north', 'bar',  22.88);

select
   region,
   sum(val) filter (where item = 'foo') foo,
   sum(val) filter (where item = 'bar') bar,
   sum(val) filter (where item = 'baz') baz
from
   tq84_data
group by
   region;

-- region   foo     bar     baz
-- -------------------------------
-- west     433.32  320.52  131.87
-- north     22.88   83.85
-- east      57.24  498.67  386.06
Github repository about-PostgreSQL, path: /sql/select/group-by/filter.sql

Index