Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE batch (
    batch_id serial NOT NULL PRIMARY KEY,
    price    varchar(50) NOT NULL
);

CREATE TABLE poster (
  poster_id serial NOT NULL  PRIMARY KEY
, country   varchar(50) NOT NULL
, batch_id  int REFERENCES batch (batch_id)  -- not serial!
);

INSERT INTO batch (price) VALUES
  (10)
, (11)
;

INSERT INTO poster (country, batch_id) VALUES
  ('DEU', 1)
, ('DEU', 1)
, ('FRA', 1)
;

CREATE TABLE



CREATE TABLE



INSERT 0 2



INSERT 0 3


SELECT b.batch_id, germany, total - germany AS other, total
FROM   batch b
LEFT   JOIN LATERAL (
   SELECT count(*) FILTER (WHERE country = 'DEU') AS germany
        , count(*) AS total
   FROM   poster p
   WHERE  p.batch_id = b.batch_id
   ) p ON true;


batch_id
germany
other
total


1
2
1
3

2
0
0
0



SELECT 2


SELECT b.batch_id
     , COALESCE(germany, 0) AS germany
     , COALESCE(total - germany, 0) AS other
     , COALESCE(total, 0) AS total
FROM   batch b
LEFT   JOIN (
   SELECT batch_id
        , count(*) FILTER (WHERE country = 'DEU') AS germany
--      , count(country = 'DEU' OR null) AS germany
        , count(*) AS total
   FROM   poster
   GROUP  BY batch_id
   ) p USING (batch_id);


batch_id
germany
other
total


1
2
1
3

2
0
0
0



SELECT 2