PostgreSQL - left join i group by

select nazwa,count(*) as liczba,round(avg(placa_pod),1) as placa
from pracownicy p
right join zespoly z on z.id_zesp=p.id_zesp
group by nazwa
order by nazwa;

\timing off
\pset pager off

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;


\c postgres;
DROP DATABASE IF EXISTS test2;
DROP ROLE IF EXISTS test2;

CREATE ROLE test2 PASSWORD 'test2' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test2 OWNER test2 ENCODING 'UTF-8';
\c test2;
set role test2;


CREATE TABLE countries (
    id   serial                NOT NULL,
    name character varying(10) NOT NULL
);


CREATE TABLE users (
    id   serial                NOT NULL,
    id_country integer         NOT NULL,
    name character varying(10) NOT NULL,
    price double precision
);


create or replace function random_string(length integer) returns text as 
$$
declare
  chars text[] := '{A,B,C,D,E,F,G,H}';
  result text := '';
  i integer := 0;
begin
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;


create or replace function mk_countries(length integer) returns void as 
$$
declare
  i integer;
begin
  for i in 1..length loop
    execute 'insert into countries (name) values( random_string(5) )' ;
  end loop;
end;
$$ language plpgsql;


create or replace function mk_users(length integer) returns void as 
$$
declare
  i integer;
  p double precision;
  cnt_country integer;
  id_country integer;
begin
  select count(*) into cnt_country from countries;
  for i in 1..length loop
    id_country = floor( random() * cnt_country ); -- zawsze przynajmniej jeden kraj ma zero userów, więc będzie zero w testach
    p = random() * 20000 + 3000;
    execute 'insert into users (id_country,name,price) values( $1 , random_string(10) , $2 )' using id_country, p;
  end loop;
end;
$$ language plpgsql;

select mk_countries(30);
select mk_users(10000);


select 
  c.name as NAZWA, 
  (select count(*) from users as u1 where u1.id_country = c.id) AS LICZBA,
  (select avg(u2.price) from users as u2 where u2.id_country = c.id) AS PLACA
from
  countries as c
order by
  c.name;
  
  
  

SELECT z.nazwa,COUNT(p.id) AS liczba,Coalesce(round(avg(placa_pod),1),0) AS placa
FROM pracownicy p
RIGHT JOIN zespoly z ON z.id_zesp=p.id_zesp
GROUP BY nazwa
ORDER BY nazwa;