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

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759612551, '216.73.216.149', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/PostgreSQL/SQL/select/group-by/filter(71): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78