Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version from rdb$database;


VERSION


3.0.10


create table clients (
  clientcode integer constraint pk_clientcode primary key,
  name varchar(50) not null
);
create table orders (
  order_id integer constraint pk_orders primary key,
  clientcode integer not null constraint fk_orders_clients references clients (clientcode),
  amount decimal(18,2)
);
insert into clients (clientcode, name) values (123, 'A');
1 rows affected

insert into clients (clientcode, name) values (234, 'B');
1 rows affected

insert into clients (clientcode, name) values (345, 'C');
1 rows affected

insert into orders (order_id, clientcode, amount) values (12, 234, 10.00);
1 rows affected

insert into orders (order_id, clientcode, amount) values (13, 123, 20.00);
1 rows affected

insert into orders (order_id, clientcode, amount) values (14, 123, 10.00);
1 rows affected

execute block returns (client_code integer,amount decimal(18,2))
as
begin
  for select clientcode from clients 
    into client_code
  do
  begin
    select sum(amount) from orders where clientcode = :client_code
      into amount;
    suspend;
  end
end


CLIENT_CODE
AMOUNT


123
30.00

234
10.00

345
null


select clients.clientcode as client_code, sum(orders.amount) as amount
from clients
left join orders on clients.clientcode = orders.clientcode
group by clients.clientcode


CLIENT_CODE
AMOUNT


123
30.00

234
10.00

345
null