SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE work
(worknumber VARCHAR(4),
workhour INTEGER);

INSERT INTO work VALUES ('E001',9);
INSERT INTO work VALUES ('E001',1);
INSERT INTO work VALUES ('E002',19);
INSERT INTO work VALUES ('E002',1);
INSERT INTO work VALUES ('E003',29);
INSERT INTO work VALUES ('E003',1);
INSERT INTO work VALUES ('E004',39);
INSERT INTO work VALUES ('E004',1);
8 rows affected

SELECT worknumber,SUM(workhour) AS hour
FROM work 
GROUP BY worknumber;


worknumber
hour


E001
10

E002
20

E003
30

E004
40


SELECT SUM(workhour)/(SELECT COUNT(DISTINCT(worknumber)) FROM work) AS Average
FROM work;


Average


25


SELECT worknumber, SUM(workhour) AS sh 
FROM work 
GROUP BY worknumber 
HAVING SUM(workhour) < (SELECT SUM(workhour)/(SELECT COUNT(DISTINCT(worknumber)) FROM work) FROM work);


worknumber
sh


E001
10

E002
20