0

I have three tables in my db which were created via the following declarations:

create table employee (
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date);

create table task (
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer references employee (id) on cascade update,
cat_id integer references category (id) on cascade update);

create table category (
id serial primary key,
name varchar (30) not null );

How ca I get the employee.id of employees who have the amount of tasks assigned to them more than the average amount of tasks assigned to employees?

0

1 Answer 1

0

First, I've reduced your tables by deleting FOREIGN KEYS just for the sake of the question.

create table task 
(
id serial primary key,
title varchar (50) not null,
created_date date default current_date,
empl_id integer,
cat_id integer
);

create table employee 
(
id serial primary key,
name varchar (20) not null,
surname varchar(30) not null,
registration_date date default current_date
);
insert into employee values
(1, 'Emp1', 'SEmp1', now()),
(2, 'Emp2', 'SEmp2', now()),
(3, 'Emp3', 'SEmp3', now());
insert into task values
(1, 'Task1', now(), 1, 0),
(2, 'Task2', now(), 1, 0),
(3, 'Task3', now(), 1, 0),
(4, 'Task4', now(), 2, 0),
(5, 'Task5', now(), 2, 0),
(6, 'Task6', now(), 3, 0);

I've used this query to calculate AVG():

select avg(tasks) as average
from   (select empl_id, count(*) as tasks from task group by empl_id) t1;
|            average |
| -----------------: |
| 2.0000000000000000 |

Then I've used it to filter employees > AVG().

select   empl_id, count(*) as tasks
from     task
group by empl_id
having   count(*) > (select avg(tasks) as average
                     from   (select   empl_id, count(*) as tasks 
                             from     task 
                             group by empl_id) t1);
empl_id | tasks
------: | ----:
      1 |     3

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.